SQL SERVER – 2008 – Insert Multiple Records Using One Insert Statement – Use of Row Constructor

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 (http://blog.SQLAuthority.com)

114 thoughts on “SQL SERVER – 2008 – Insert Multiple Records Using One Insert Statement – Use of Row Constructor

  1. Hi ,
    I want to update data into second server from first server ,without using linked server .
    I want to use openrowset.
    Can any one tell me how o do this ?

    Like

  2. I always wanted SQL Server to accepts this kind of SQL Statements for INSERTS, finally they have come up with these…

    Thanks again.

    Like

  3. The syntax should be

    INSERT INTO MyTable (FirstCol, SecondCol)
    VALUES
    (‘First’,1),
    (‘Second‘,2),
    (‘Third‘,3),
    (‘Fourth‘,4),
    (‘Fifth‘,5)

    Like

  4. that’s good for new generation because after upgrade on sql 2005 that is new version as discuss mr. drel as example
    ……………..
    so i appreciate to him.
    haing a nice job.

    Like

  5. Pingback: SQL SERVER - Insert Multiple Records Using One Insert Statement - Use of UNION ALL Journey to SQL Authority with Pinal Dave

  6. There is an easier way to do this.
    If you are inserting a single row then use
    insert into(a1,b1) values(1,2)

    If inserting multiple rows using select statement use,

    Insert into(a1,b1) (select a1,b1 from c)

    Like

  7. Hi,
    Is below syntax works in sql server 2005 or it works only in sql server 2008?
    iam getting some error when i try to use it in sql server-2005

    INSERT INTO MyTable (FirstCol, SecondCol)
    VALUES (‘First’,1),
    (‘Second’,2),
    (‘Third’,3),
    (‘Fourth’,4),
    (‘Fifth’,5)

    Like

  8. how to retrive deleted rows in sql server(in oracle having FLASHBACK QUERY),i know only we have an consistant backup that only restore the data.
    any other feature do u know pls tell me
    thanks and regards
    Rajasekar

    Like

  9. Hi Rajasekar,

    You can not get deleted Rows in Sql server.

    You can get it only in Trigger in “deleted table” or you can use “instead of trigger”

    Tejas

    Like

  10. I have a question about this new INSERT command in SQL Server 2008. Is it limited to only 1000 enteries? I have a lot more and I am getting the following error:
    The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values

    Like

  11. This is nice one but we can also insert more than one row as below:-

    Insert into tableName(col1,col2) values(1,2),(11,22),(44,55);

    Out Put
    Col1 Col2
    1 2
    11 22
    44 55

    Thanks………

    Like

  12. Hi,
    There are other alternatives and better approaches than multiple statements shown here.

    For example, a single INSERT query will not let you process the parameters before insertion, which makes this method weak. Using triggers will work, but will degrade your server performance.

    So if your data needs no processing, you can use multiple insert statements. However, if you want to process them then I’d recommend using one of the following two new features since SQL Server 2005:

    1. Using OPENXML
    2. CLR User Defined Types

    Just google them and you will find many examples and tutorials.

    Like

  13. Thank you for the brief article. It was brief and to the point.
    I have used this syntax with other tools. It is ideal for loading initial definition data, and test data. Its simplicity is its strength.

    Like

  14. @fred

    Insert into table2 select * from table1
    GO 1000

    above statement will insert everything from table1 into table2 1000 times. Just change the number beside GO and and you are good.

    This will work in SQL Server 2005/2008.

    ~ IM.

    Like

  15. With sql server CE this query:

    INSERT INTO Scontistica
    (Prodotto,Sconto)
    values (‘Sky’,0.5),
    (‘Skywarm’,0.5)

    give me this error:
    [ Token line number = 2,Token line offset = 19,Token in error = , ]

    why??

    Like

  16. Hello Prashant,

    As MS Excel, SQL Server doesn’t allow us to define the position of new row. But it can be done indirectly by creating clustered index. In a heap table we have no control to the physical storage location of a row.

    Kind Regards,
    Pinal Dave

    Like

    • Well, of course you can.

      There was an article about setting IGNORE_DUP_KEY ON. Thats one way to do it. Distinct values are added to the table and duplicate values are ignored. Just remember to set IGNORE_DUP_KEY OFF when you’re done or you can get yourself into all kinds of troubles afterwards.

      Other way, which I prefer more, is simply just to select only distinct values into the other table. For example, and this is very narrow example:

      INSERT INTO TargetTable
      SELECT DISTINCT * FROM SourceTable

      Like

  17. Hello,

    I am very new to SQL Server. I hope someone can help me. I have a large fixed width flat file that I need to import into an existing table in SQL Server 2008. I’ve tried using the wizard, but it either imports everything into the first column as one long string, or it defines the first column as 1 digit and imports everything into that column in multple rows. I was told that if I import the file as one long string, then I could write a statement that would allow me to import it into the table I’ve built in the proper (correct columns) format. I am not sure exactly which statement would be best for this. I will need to do this once or twice a week, so I only want to have to write the query once. I know this has to be possible, but I’m at a total loss.

    Would it be better to use an update statement? For example
    This is where I get confused. I am not sure of the correct syntax to use.

    Use my_database
    Update (my_table)
    Set first_column varchar(4) not null = long_string_table.column_1;
    Set second_column varchar(1) not null = long_string_table (do I need to say where each column begins and ends? That seems to make more sense.)

    Any help is appreciated. Thank you.

    Like

  18. when i use the query the
    INSERT INTO MyTable (FirstCol, SecondCol)
    VALUES (‘First’,1),
    (‘Second’,2),
    (‘Third’,3),
    (‘Fourth’,4),
    (‘Fifth’,5)
    in sql server 2005 it is giving error
    Incorrect syntax near ‘,’. ca just tell me it will work with 2005 also ? or it will work with only 2008

    Like

  19. I have inserted the following into MS Access:

    INSERT INTO ms_Material (MUID, Manning, EqRough, HWCoef)
    VALUES
    (‘CCP’, 0.0115, 0.0082, 120)
    (‘CFL PCCP’ , 0.0115 , 0.0082 , 120)
    (‘PCCP’ , 0.0115 , 0.0082 , 120)
    (‘RCP’ , 0.0115 , 0.0082 , 120)
    (‘Steel RGJ’ , 0.0115 , 0.0082 , 120)
    (‘Steel Relined PCCP’ , 0.0115 , 0.0082 , 120)
    (‘WSP’ , 0.0115 , 0.0082 , 120)
    (‘WSP CML’ , 0.0115 , 0.0082 , 120)
    (‘WSP CML&DC’ , 0.0115 , 0.0082 , 120);

    Can someone please give me a reason why I am getting syntax error messages? Thank you.

    Like

    • This syntax is not supported in MS Access
      You need to use multiple insert statements

      INSERT INTO ms_Material (MUID, Manning, EqRough, HWCoef)
      VALUES
      (‘CCP’, 0.0115, 0.0082, 120)

      INSERT INTO ms_Material (MUID, Manning, EqRough, HWCoef)
      VALUES
      (‘CFL PCCP’ , 0.0115 , 0.0082 , 120)

      .
      .
      .

      Like

  20. how to insert multiple rows using single query in sql 2000 and 2005 ?

    when i use the query the
    INSERT INTO MyTable (FirstCol, SecondCol)
    VALUES (‘First’,1),
    (‘Second’,2),
    (‘Third’,3),
    (‘Fourth’,4),
    (‘Fifth’,5)
    in sql server 2005 it is giving error
    Incorrect syntax near ‘,’ .

    is ther any other format similar to the above one for inserting multiple rows ins sql 2000 and 2005 ?

    Like

  21. i am working on sql 2000, now i want to transfer my entire data on sql 2005. can u pls provide me the steps that how i have to take the backup and how to implement it in 2005.

    Like

  22. Hi,

    [Sql 2005]

    how i copy one table data (above 8 lakhs rows) into another one table within the server using T-SQL (except insert statement) ?

    Can you provide its solutions…? It must helpful for me

    Thank you

    Like

  23. hi,

    this question might be naive but im trying to insert two values in a single column

    there are two columns
    tablename:exmaple
    t1—–id
    t2—parentid

    can i insert two parentid for a single cell?

    insert into example values(‘1′,’23,24′);

    im getting a error message Incorrect syntax near ‘,’.

    thanks for your time and would appreciate any help

    Like

  24. 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

    Like

  25. 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’

    Like

  26. 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.

    Like

  27. 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?

    Like

  28. 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.

    Like

  29. 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 ‘,’

    Like

  30. 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

    Like

  31. 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?

    Like

  32. 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

    Like

  33. 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.

    Like

  34. 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!

    Like

  35. 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…

    Like

  36. I have a table CPY10100 with a missing column. So I made a temp file of the table and dropped/created a new table CPY10100 with the correct columns.
    So how do I use the INSERT INTO SELECT command in SQL2008 to bring the data from the CPY10100temp to the CPY10100?

    I’ve tried:

    INSERT INTO CPY10100
    (
    list of all columns, including new column
    )
    SELECT
    (
    list of all columns,including new column
    )
    FROM CPY10100temp

    but get an error on the newly created column.

    Your help would be much appreciated.

    Thomas

    Like

  37. My enivironment is Sql Server 2008 R2, but still i’m getting error: Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘,’.

    here is my code:
    CREATE TABLE #SalesDates
    (id INT, saledate DATEtime
    );
    go
    INSERT INTO #SalesDates (id,saledate)
    VALUES
    (1, ‘2012/1/1′),
    (2, ‘2012/1/2′),
    (3, ‘2012/1/3′),
    (4, ‘2012/1/4′),
    (5, ‘2012/1/5′),
    (6, ‘2012/1/6′)
    go
    SELECT id
    FROM #SalesDates s
    WHERE s.saledate BETWEEN ‘2012/1/1′ AND ‘2012/1/6′;
    go
    DROP TABLE #SalesDates;

    please help me.

    Like

  38. This doesn’t make sense at all. I keep gettting the Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘´’. error message when I try. Please help.

    Like

  39. I am using this method to insert multiple recoreds, but when one of the rows has an error it does not tell me which one. How to find out which of the 1000 rows has the error??

    Like

  40. Pingback: SQL SERVER – Three Methods to Insert Multiple Rows into Single Table – SQL in Sixty Seconds #024 – Video « SQL Server Journey with SQL Authority

  41. Hi,
    need ur help, how i want to create query to insert value in others server?which have sql instance name. ex: server1\sql01

    Sql query if insert value in same server, this ok;
    insert into dbo.udtFGPALLET (palletID) values (@PackageSN)

    Sql query if insert value in other server with sql instance;

    insert into server1\sql01.rpt_db.dbo.udtFGPALLET (palletID) values (@PackageSN)

    result error bcoz of \ … pls help. thanks

    Like

  42. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

  43. If we have 1 lakh records to update then it is difficult to add all those in row construction scenario.is there any other solution please reply me

    Like

  44. Is it possible to insert multiple records in multiple tables using single select query ?
    Like,
    CREATE TABLE Relation
    (
    ProductId INT,
    SubCategoryId INT
    )

    CREATE TABLE Category
    (
    MainCatId INT,
    SubCategoryId INT
    )

    I want something like this, Is it possible ?

    INSERT INTO Relation
    (ProductId,MainCatId)
    INSERT INTO Category
    (MainCatId,SubCategoryId)
    SELECT ProductId,MainCatId,SubCategoryId
    FROM ProductDetails

    Thanks.

    Like

  45. getting error in 2nd time same query, first time same query worked properly in sql server 2012:
    PLEASE TELL ME THE SOLUTION

    error query:

    Update [churn_trial_F] set Status_by_site=’Customer Present In 2012/2013’
    where [PT_Fiscal_Year] = ‘BN\2011′ and [$(getting error : ” incorrect syntax near ‘\’ “)]

    [Internal_External]!= ‘Internal MCS (DEPT)’ [$(getting 2 error : ‘ incorrect syntax near ‘internal ” and “incorrect syntax near ‘DEPT'” EXPECTING ‘(‘ OR SELECT.)]
    and [Total_Revenue] >0
    and [Status_by_site] is null
    and [Customer_by_site] in

    (Select a.[Customer_by_site] from
    Trial_d2 a
    inner join Trial_d2 b on a.[Customer_by_site] =b.[Customer_by_site]
    where a.year=’2011′
    and b.year=’2012′ [$(getting error :UNCLOSE QUOTATION MARK AFTER THE CHARACTER STRING ” ) ‘ .]
    )

    #1ST QUERY SUCESSFULLY WORKED:

    Update [churn_trial_F] set [Status_by_Site] =’Loyal Customer’
    where [PT_Fiscal_Year] in(‘BN/2011′,’BN/2012′,’BN/2013′) and
    [Internal_External]!=’Internal MCS (DEPT)’
    and [Total_Revenue] >0
    and [Status_by_Site] is null
    and [Customer_by_site] in
    (Select a.[Customer_by_site] from
    Trial_d2 a
    inner join Trial_d2 b on a.[Customer_by_site]=b.[Customer_by_site]
    inner join Trial_d2 c on b.[Customer_by_site]=c.[Customer_by_site]
    where a.year=’2011′
    and b.year=’2012′
    and c.year=’2013′
    )

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s