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 (https://blog.sqlauthority.com)

SQL Scripts
Previous Post
SQLAuthority News – Microsoft Most Valuable Professional Award for SQL Server – MVP
Next Post
SQL SERVER – 2008 – Introduction to New Feature of Backup Compression

Related Posts

120 Comments. Leave new

  • They-bid Curve-in
    October 12, 2011 6:45 pm

    hi,
    how to insert another rows in table that contain primary and foreign key?
    because when i insert some rows, i am getting wrong.. i need your answer please…

    thanks

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

    Reply
  • 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.

    Reply
  • 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.

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

    Thankx,
    sruthi

    Reply
  • Thanks! Was helpful.

    Reply
  • Howard Joslin
    July 21, 2012 12:43 am

    Thanks. I find that your site usually has the best and easiest to understand instructions.

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

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

    Reply
  • There should be a way to like this with Windows Live Credentials. This post saved me some work :-)

    Reply
  • Nuthan Murarysetty
    January 28, 2014 10:25 am

    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

    Reply
  • Rakesh Pithawa
    May 28, 2014 12:21 pm

    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.

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

    Reply
  • How to insert 10000 records at a time using insert statement ?

    Reply
    • Insert into table values (‘a’)
      go 10000

      Reply
      • Thank you for reply……i want insert records at a time morethan 100 using single statement ?Is it possiable ?

      • I am inserting 5 in one statement.

        use tempdb
        go
        Create table x (i int, j varchar(100))
        go
        Insert into x values (1,’One’), (2, ‘Two’), (3, ‘Three’), (4, ‘Four’), (5, ‘Five’)
        go
        Select * from x
        go
        drop table x

  • Need to fetch the column value and error message of insertion fails while inserting multiple records using insert into select * query

    Reply
  • Hi Pinal Dave

    I have .sql file which contains millions of Insert commands.,and they are having insert statements to be inserted into different tables When I am executing by opening in SQL SERVER MANAGEMENT it says

    Insufficient memory to continue the execution of program
    Can anyone please help me its very urgent

    Thanks in advance for your help

    Reply
  • Hi ,

    I need to select 2 coulmns from one table and it should be inserted into newly cretaed variable not table in single query how can i write query fro this

    Reply

Leave a Reply