SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL

Update: In SQL Server 2008 there is an even better method of Row Construction for inserting multiple records, please read it here: SQL SERVER Insert Multiple Records Using One Insert Statement – Use of Row Constructor

This is a very interesting question I have received from new development. How can I insert multiple values in a table using only one insert? Now this is an interesting question. When there are multiple records are to be inserted in the table following is the common way using T-SQL.

Solarwinds

SQL SERVER - Insert Multiple Records Using One Insert Statement - Use of UNION ALL fullouter_join

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

The clause INSERT INTO is repeated multiple times. Many times DBA copy and paste it to save time. There is another alternative to this, which I use frequently. I use UNION ALL and INSERT INTO … SELECT… Clauses. Regarding performance there is not much difference. If there is performance difference it does not matter as I use this for one time insert script. I enjoy writing this way, as it keeps my focus on the task, instead of copy paste. I have explained following script to new developers. He was quite pleased.

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

The effective result is same.

Reference : Pinal Dave (https://blog.sqlauthority.com) , SQL SERVER – Union vs. Union All – Which is better for performance?

Solarwinds
, , , ,
Previous Post
SQL SERVER – 2005 Download New Updated Book On Line (BOL)
Next Post
SQL SERVER – UDF – Function to Display Current Week Date and Day – Weekly Calendar

Related Posts

876 Comments. Leave new

  • table1 has one record
    table2 has one record
    these record are not same but field name is same
    when these two tables are union, only one record is received.
    please help me

    Reply
  • one table for example marksheet use id = pk and rollno use table insert data roll no is same no are display in dtabase .how to solved that rollno is not same enter to insert time

    Reply
  • How can I insert more than 1000 rows in a temp table

    Reply
  • ashish mishra
    June 12, 2016 11:52 am

    sir I am stuck in a logic ..
    I have dynamic tables . lets say one large data Big_Table is stamped with profile 1,2,3,….n
    so i create table profile1,profile2,… profilen (example profile1 :- select * from Big_Table where rule = profile1)
    table data example :-
    table_profile1
    transaction_id profile priority
    1 1 1
    2 1 1

    table_profile2
    transaction_id profile priority
    1 2 2
    3 2 1
    now lets say we got single transaction in Big table is repeated in table_profile1 and table_profile2
    so i need to pick all data of table_profile1,table_profile2,table_profilen with lower Priority
    so here in target transaction_id 2 is repeated in profile1 and profile2 table so final table will have only transaction id 2 with lower priority of profile 1 as its 1
    Big_table_stamped
    transaction_id profile priority
    1 1 1
    2 1 1
    3 2 1
    so this final stamped table Big_table_stamped

    query i am dynamically creating is

    select transaction_id,profile,priority
    from (
    select row_number() over (partition by a.transaction_id order by a.priority asc) r
    a.*
    from (
    select * from table_profile1
    union all
    select * from table_profile2
    union all
    .
    union all
    select * from table_profilen
    ) a
    ) data
    where data.r=1
    )

    but this is slow … i may have 600 tables too..
    sholud i first put all data in one table ..
    we broke tables to make faster process
    should i join all tables .
    or what should be my approach
    I dont have much time

    Reply
  • how to insert more than 500 records into table without duplicates

    Reply
  • There seems to be no need to use either extra insert statements or union clauses. Why do you prefer the use of union?

    My way to insert multiple records would look like this:

    insert into TESTNILS (ZAHL, TEXT) values (1,’eins’), (2,’zwei’), (3, ‘drei’), ….

    Nils

    Reply
    • When this article was written at that time the new syntax which you suggested was available in SQL Server.

      I hope this helps.

      Reply
      • Goran Stevanovic
        April 19, 2017 6:43 pm

        Hi Pinal,

        I just saw your tweet with the link to this post. To make it more valuable, I would suggest adding this example:

        INSERT INTO MyTable  (FirstCol, SecondCol) VALUES (‘First’,1), (‘Second’,2), (‘Third’,3);

        Regards,

        Goran

      • Great suggestion.

  • Great one

    Reply
  • Suppose I have a list of comma separated numbers

    123,
    345,
    888,
    999

    I want to insert them in a temp table, how would I be able to do this?

    Reply
  • Sir, What to do in case if I want to insert IMAGE also (as byte array) ? How can I use the same syntax ? Is there any idea?

    Reply
  • MohammedAshrafali
    September 5, 2017 4:43 pm

    Can we use xml for multiple insert?

    Reply
  • MohammedAshrafali
    September 5, 2017 4:44 pm

    Thankyou, It is great suggestion.

    Reply

Leave a Reply

Menu