I previously wrote article about SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL. I am glad that in SQL Server 2008 we have new feature which will make our life much more easier. We will be able to insert multiple rows in SQL with using only one SELECT statement.
Previous method 1:
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Second',2);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Third',3);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fourth',4);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fifth',5);
GO
Previous method 2:
USE YourDB
GO
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
SQL Server 2008 Method of Row Construction:
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
Reference : Pinal Dave (https://blog.sqlauthority.com)
120 Comments. Leave new
How could I make a Row Construction from two(or more) separeted UNION ALL…I mean…
First col is result from a
select [field] from x UNION ALL
select [field] from x UNION ALL, etc…(like your example above) but, and the second column could be from another table and another field
select [field] from w UNION ALL
select [field] from w UNION ALL
.Tanks in advance
You need to use a join to get another column
Hi,
Thank you for this informative article.
Hi,
I am using sql server management studio 2008. When I try to insert multiple rows using row constructor, it gives me an error.
Suppose say, I create a table using following syntax.
CREATE TABLE [dbo].[TblCustomer](
[CustomerId] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] varchar(50) NOT NULL,
[CustomerArea] varchar(50) NOT NULL,)
If I insert 1 row, query gets executed correctly. But whenI insert multiple rows, it fails saying
“Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ‘,’.
I used following.
Insert into tblcustomer ([CustomerName],[CustomerArea]) values
(‘Tejas’,’ABC’),
(‘Jack’,’XYZ’)
If I remove second entry (jack & xyz) it will work.
Pls let me know if i am going wrong anywhere. Its lengthy to use ‘Union’
What is the compatibility level of the database?
It should be 100
EXEC sp_helpdb ‘your_db’
Hi Madhivanan,
Very sorry for the delay.
I checked the compatibility level, And its 90. Now I am searching if I can change the compatibility without any harm to the database?
Make sure to read this blog post
how can i delete column in sql server 2005.
If you want to permanently delete a column, use drop command
alter table table_name
drop column column_name
Hi
I use sql server 2008 express R2, when I tried to use “insert into myTable (column1, column2,…..columnN) values
(value01, value02,….value0N),
(value11, value12,….value1N),
(value21, value22,….value2N),
(value31, value32,….value3N),
……
(valueM1, valueM2,….valueMN)” syntax to insert hundreds of rows into the table by one insert into statement,
it runs very slower when M increased and timeout with get
Msg 8623 “The query processor ran out of internal resources and could not produce a query plan. ” error when M greater than a few hundreds.
any ideas how to improve it? your help is very appreciate.
Note that using that method you can only add 1000 sets of data.
Hi.
Getting my SQL 2008 Dev Ed next week (only just needing to move to this).
Inserting multiple rows in 1 statement is very useful. Is there an INSERT OR UPDATE mechanism that works for multiple rows?
Hi Pinal Dave,
As per this post i found that your second query->
SQL Server 2008 Method of Row Construction:
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1),
(‘Second’,2),
(‘Third’,3),
(‘Fourth’,4),
(‘Fifth’,5)
It is not working, it seems that this have some suntaxical error.
Hi Pinal Dave,
I use SQL Server 2008 R2. I get error when running this query.
INSERT INTO [myTable]
([id],
[col1],
[col2],
[col3])
VALUES (24888,6,548,1020),
(24888,2,548,1020),
(24888,5,548,1020),
(24888,0,548,1020)
ERROR : Incorrect syntax near ‘,’
Make sure the compatibility of the database is 100. Otherwise it will not work
For User Using SQL 2000/2005
You can use the following statement to have same effect as of 2008 feature …
Insert Into Table1 (col1,col2) Values (‘A’, ‘B’ ) Insert Into Table1 (col1,col2) Values (‘C’, ‘D’ )
Multiple inserts requried.
Hope it helps
I use SQL Server 2008 R2. I get error when running this query.
INSERT INTO [myTable]
([id],
[col1],
[col2],
[col3])
VALUES (24888,6,548,1020),
(24888,2,548,1020),
(24888,5,548,1020),
(24888,0,548,1020)
ERROR : Incorrect syntax near ‘,’
————————————————
compatibility of the my database is 90.
then what i have to do?
How it will be 100. Is there any way?
Use the code
ALTER DATABASE your_DB_name
SET COMPATIBILITY_LEVEL = 100
PinalDave,
Your tech editor from SQL Server Magazine days here…this works if you’re using SQL 2008 Management Studio against a SQL 2005 database. I haven’t needed to use this construct until today, when I was tasked with inserting a bunch of data from a spreadsheet into a table on a SQL Server 2005 database. Working remotely (VPN), I kept running into the issues of SQL Server whining about not being able to find the file (because it wasn’t local, or because it couldn’t resolve the UNC path), and then the security issues (not my database so there wasn’t much I could do about that one!). I realized that I was wasting too much time trying to set up the bulk insert, and thought “can I do the multi-row insert, even tho this is a 2005 database?” Yup, it worked. Thought you’d like to know…
Best,
Michelle Poolet
I have another question for you all….
I have 2 tables, both with IDENTITY colums as the first field.
Table1: address_id, street, city, state zip
Table2: Customer_id, name, address_id
How can insert multiple records to Table1, retrieving the IDs so I can then insert into table 2? I need to do this:
(a) With minimal locking on the tables
(b) Matching existing records and returning the old “address_id”
(c) Get back all of the new “Customer_id” values once I am done.
Please advise.
good article….. :)
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1);
except this remaing above syntax show’s error
What was the error? Also post the full code you have used
Just to note as well, there is a 1000 entry limit as well so you can’t do this for more than 1000 entries (found out after trying to insert 4000).
Does anybody know how can we retrive the ids of the inserted rows in this case?
Do you want to know the last id inserted? Use scope_identity() function
Thanks alot for your query
Hi all,
I was just wondering about the difference between SQL Server 2008 R2, SQL Server 2008 and SQL Server 2005 in terms of sql syntax? And also regarding the installation procedure. Are there any pre-requisites required? (Eg. Microsoft .NET Framework 3.5 ?) and how about the silent installation of SQL Server 2008 R2? Are there any?
Hoping for answers. Thanks!
the select query is not execute in my SQL server 2008..while running the select query i m getting the error msg as “An error occurred while executing batch. Error message is: Field token out of range.”
anybody pls help on this…
Post the full code you have used. Also which version of SQL Server are you using?
i have using the below version
Microsoft SQL Server 2008 (SP2) – 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)
any update????
Post the full code you used so that it is easy to identify the error
Thanks….