Insert multiple values using INSERT INTO (SQL Server 2005)
Solution 1
The syntax you are using is new to SQL Server 2008:
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
VALUES
(1000,N'test'),(1001,N'test2')
For SQL Server 2005, you will have to use multiple INSERT
statements:
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
VALUES
(1000,N'test')
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
VALUES
(1001,N'test2')
One other option is to use UNION ALL
:
INSERT INTO [MyDB].[dbo].[MyTable]
([FieldID]
,[Description])
SELECT 1000, N'test' UNION ALL
SELECT 1001, N'test2'
Solution 2
You can also use the following syntax:-
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO
From here
Solution 3
In SQL Server 2008,2012,2014 you can insert multiple rows using a single SQL INSERT statement.
INSERT INTO TableName ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )
Another way
INSERT INTO TableName (Column1, Column2 )
SELECT Value1 ,Value2
UNION ALL
SELECT Value1 ,Value2
UNION ALL
SELECT Value1 ,Value2
UNION ALL
SELECT Value1 ,Value2
UNION ALL
SELECT Value1 ,Value2
Related videos on Youtube
Ben McCormack
Becoming a better version of myself. Website: http://benmccormack.com Twitter: @bmccormack I find great satisfaction in developing solutions that make it easier and simpler for people to do their jobs. I haven't updated by Stack Overflow CV in forever, so check out my LinkedIn page.
Updated on July 05, 2022Comments
-
Ben McCormack about 2 years
In SQL Server 2005, I'm trying to figure out why I'm not able to insert multiple fields into a table. The following query, which inserts one record, works fine:
INSERT INTO [MyDB].[dbo].[MyTable] ([FieldID] ,[Description]) VALUES (1000,N'test')
However, the following query, which specifies more than one value, fails:
INSERT INTO [MyDB].[dbo].[MyTable] ([FieldID] ,[Description]) VALUES (1000,N'test'),(1001,N'test2')
I get this message:
Msg 102, Level 15, State 1, Line 5 Incorrect syntax near ','.
When I looked up the help for INSERT in SQL Sever Management Studio, one of their examples showed using the "Values" syntax that I used (with groups of values in parentheses and separated by commas). The help documentation I found in SQL Server Management Studio looks like it's for SQL Server 2008, so perhaps that's the reason that the insert doesn't work. Either way, I can't figure out why it won't work.
-
Malachi about 11 yearsJust Curious if you tried it without the
,
between the value sets? I know that this post is fairly old, but I am Curious. I found this when I was searching for an answer, and most of my SQL knowledge is within the past couple of years. -
Michel Ayres over 10 years
-
-
aheil almost 12 yearsThank you very much for this. We used SQL 2005 for the testing system while we developed on SQL 2008. Some of the developers were not aware of the fact this syntax is only available on SQL 2005.