Insert multiple values using INSERT INTO (SQL Server 2005)

sql, sql-server, sql-server-2005, t-sql

Solution

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'

Problem

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.

Original source

Related problems