SQL Insert statement with multiple rows

Question

This seems really trivial, but for the life of me I cannot understand why this is not working. Any ideas would be great!

USE [ACCTQA]
GO

INSERT INTO [dbo].[glacct]
           ([Company]
           ,[Account]
           ,[Description])
     VALUES
 ('E096','191802','INVESTMENT IN SUBS')
,('E096','320007','COMMON STOCK')
,('E096','330013','SURPLUS')
,('E096','340003','RETAINED EARNINGS')
,('E096','690876','LIAB & CASUALTY INS')
,('E096','710000','BONDS - AGENCY BILL')
,('E096','710001','COMM. P&C - AGENCY BILL')
,('E096','710002','EMPL BENEFITS-AGENCY BILL')

ERROR MSG:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ','.

Which version of SQL Server you are using? It works for SQL Server 2008 and above sqlfiddle.com/#!3/fd272 — Rahul Tripathi
– Rahul Tripathi, Commented Feb 10, 2016 at 13:42
if you use SQL Server CE, than it doesn't support bulk insert as I know — demo
– demo, Commented Feb 10, 2016 at 13:43

sagi · Accepted Answer · 2016-02-10 13:53:58Z

1

Try this

USE [ACCTQA]
GO

INSERT INTO [dbo].[glacct]
           ([Company]
           ,[Account]
           ,[Description])
SELECT * FROM (   
SELECT 'E096','191802','INVESTMENT IN SUBS' UNION ALL
SELECT 'E096','320007','COMMON STOCK' UNION ALL
SELECT 'E096','330013','SURPLUS' UNION ALL
SELECT 'E096','340003','RETAINED EARNINGS'
.....)

I'm guessing your version is not accepting multiple values like this, so you have to use a select statement(INSERT FROM SELECT) to make it work.

edited Feb 10, 2016 at 13:53

answered Feb 10, 2016 at 13:47

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rahul Tripathi · Accepted Answer · 2016-02-10 13:49:18Z

1

Your query won't work if you are using SQL Server 2005 or below. On those version you have to try like

INSERT INTO glacct
  ([Company],[Account],[Description]) 
SELECT 'E096','191802','INVESTMENT IN SUBS'
UNION ALL
SELECT 'E096','320007','COMMON STOCK'
UNION ALL
SELECT 'E096','330013','SURPLUS'

Your query will work on SQL Server 2008 and above,.

SQL FIDDLE DEMO

edited Feb 10, 2016 at 13:49

answered Feb 10, 2016 at 13:41

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

2 Comments

sagi Over a year ago

That's my answer.. are you serious?

AG2275 Over a year ago

This only inserted the first 366 rows. No errors, states that query executed successfully?

Andy K · Accepted Answer · 2016-02-10 14:00:41Z

0

It did the trick...

CREATE TABLE #glacct (Company VARCHAR(100),
                        Account INT,
                    Description VARCHAR(200))

INSERT INTO [dbo].[#glacct]
           ([Company],
            [Account],
            [Description])
     VALUES
('E096','191802','INVESTMENT IN SUBS'),
('E096','320007','COMMON STOCK'),
('E096','330013','SURPLUS'),
('E096','340003','RETAINED EARNINGS'),
('E096','690876','LIAB & CASUALTY INS'),
('E096','710000','BONDS - AGENCY BILL'),
('E096','710001','COMM. P&C - AGENCY BILL'),
('E096','710002','EMPL BENEFITS-AGENCY BILL')

PS: Done with SQLServer 2014 ...

edited Feb 10, 2016 at 14:00

answered Feb 10, 2016 at 13:54

Andy K

5,10511 gold badges60 silver badges92 bronze badges

Collectives™ on Stack Overflow

SQL Insert statement with multiple rows

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related