insert into using Values

Question

I am am using sql server 2005 and doing a simple insert into and getting an incorrect syntax error. I See nothing wrong with my code Can someone give me some ideas what could be wrong with it?

insert into inonhd 
(fpartno,fpartrev,flocation,fonhand,fcudrev)
Values
  ('CRV109','1','11','01','1'), 
  ('CRV110','0','11','01','0')

the error is Incorrect syntax near ','.

Which version of SQL Server are you using? What are the datatypes for the columns? — Joseph B, May 20 '14 at 16:15
Read [This Thread](http://stackoverflow.com/questions/2462517/insert-multiple-values-using-insert-into-sql-server-2005). Maybe it helps. — Jens, May 20 '14 at 16:16
Thanks that thread answered my question. I am using 2005 so I have to do it one by one. — Turtleman10, May 20 '14 at 16:34

score 4 · Accepted Answer · edited May 20 '14 at 17:10

4

You must add each row in separate command.

insert into inonhd 
(fpartno,fpartrev,flocation,fonhand,fcudrev)
Values
('CRV109','1','11','01','1')

and:

insert into inonhd 
(fpartno,fpartrev,flocation,fonhand,fcudrev)
Values
('CRV110','0','11','01','0')

edited May 20 '14 at 17:10

Majid

13,853
15
77
113

answered May 20 '14 at 16:22

Ahmad

906
11
27

1

SQL 2008 and later supports [multiple inserts in one statement](http://technet.microsoft.com/en-us/library/dd776382.aspx) – D Stanley May 20 '14 at 16:24

score 0 · Answer 2 · answered May 20 '14 at 17:06

It is really important to note that the syntax in the question is fine for more recent versions of SQL Server. This is acceptable:

insert into inonhd(fpartno, fpartrev, flocation, fonhand, fcudrev)
    Values ('CRV109','1','11','01','1'), 
           ('CRV110','0','11','01','0');

If you want to do this in one statement, you can use select . . . union all:

insert into inonhd(fpartno, fpartrev, flocation, fonhand, fcudrev)
    select 'CRV109','1','11','01','1' union all
    select 'CRV110','0','11','01','0';

Of course, multiple inserts are another possibility.

insert into using Values

2 Answers2