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)

About these ads

112 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 ?

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

    Thanks again.

  3. The syntax should be

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

  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.

  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)

  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)

  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

  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

  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

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

  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.

  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.

  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.

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

  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

  17. hi,

    You are doing great job, I really appreciate it. :)

    I want to insert unique records in one insert statement. Is it possible? Thanks

    • 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

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

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

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

    • 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)

      .
      .
      .

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

    • You need to use multiple insert statements

      INSERT INTO MyTable (FirstCol, SecondCol)
      VALUES (‘First’,1)

      INSERT INTO MyTable (FirstCol, SecondCol)
      (‘Second’,2)

      etc

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

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

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

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

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

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

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

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

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

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

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

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

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

  35. INSERT INTO MyTable (FirstCol, SecondCol)
    VALUES (‘First’,1);

    except this remaing above syntax show’s error

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

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

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

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

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

  41. Hi
    how to insert multiple rows into table using one procedure call
    can anyone provide me the syntax for it

    Thankx,
    sruthi

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

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

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

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

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

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