Menu
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.
831
Insertion in a table is a DML (Data manipulation language) operation in SQL. When we want to store data we need to insert the data into the database. We use the INSERT statement to insert the data into the database.
If you want to insert more rows than that, you should consider using multiple INSERT statements, BULK INSERT or a derived table. Note that this INSERT multiple rows syntax is only supported in SQL Server 2008 or later. To insert multiple rows returned from a SELECT statement, you use the INSERT INTO SELECT statement.
The INSERT statement also allows you to insert multiple rows into a table using a single statement as the following: INSERT INTO table_name(column1,column2…) VALUES (value1,value2,…), (value1,value2,…), … In this form, you need to provide multiple lists of values, each list is separated by a comma.
Answer. Yes, instead of inserting each row in a separate INSERT statement, you can actually insert multiple rows in a single statement.
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' 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
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
