SQL SERVER – Three Methods to Insert Multiple Rows into Single Table – SQL in Sixty Seconds #024 – Video

One of the biggest ask I have always received from developers is that if there is any way to insert multiple rows into a single table in a single statement. Currently when developers have to insert any value into the table they have to write multiple insert statements. First of all this is not only boring it is also very much time consuming as well. Additionally, one has to repeat the same syntax so many times that the word boring becomes an understatement.

In the following quick video we have demonstrated three different methods to insert multiple values into a single table.

-- Insert Multiple Values into SQL Server
CREATE TABLE #SQLAuthority (ID INT, Value VARCHAR(100));

Method 1: Traditional Method of INSERT…VALUE

-- Method 1 - Traditional Insert
INSERT INTO #SQLAuthority (ID, Value)
VALUES (1, 'First');
INSERT INTO #SQLAuthority (ID, Value)
VALUES (2, 'Second');
INSERT INTO #SQLAuthority (ID, Value)
VALUES (3, 'Third');

Clean up
-- Clean up
TRUNCATE TABLE #SQLAuthority;

Method 2: INSERT…SELECT

-- Method 2 - Select Union Insert
INSERT INTO #SQLAuthority (ID, Value)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third';

Clean up
-- Clean up
TRUNCATE TABLE #SQLAuthority;

Method 3: SQL Server 2008+ Row Construction

-- Method 3 - SQL Server 2008+ Row Construction
INSERT INTO #SQLAuthority (ID, Value)
VALUES (1, 'First'), (2, 'Second'), (3, 'Third');

Clean up
-- Clean up
DROP TABLE #SQLAuthority;

Related Tips in SQL in Sixty Seconds:

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

If we like your idea we promise to share with you educational material.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

23 thoughts on “SQL SERVER – Three Methods to Insert Multiple Rows into Single Table – SQL in Sixty Seconds #024 – Video

  1. Using of BULK INSERT

    STEP 1: Create a text file the name STUDENTS_LIST.txt and add the bellow data

    1,RAJIV,M.S
    2,VINAY KUMAR,B.A
    3,HANU,M.B.A
    4,ARUN,M.Sc

    STEP 2: Create a table using bellow statment

    CREATE TABLE #STUDENT(ID INT, [NAME] NVARCHAR(50), EDUCATION NVARCHAR(20))

    STEP 3: Now use the Bulk Insert Statement

    BULK INSERT #STUDENT
    FROM ‘D:\STUDENTS_LIST.txt’
    WITH ( FIELDTERMINATOR = ‘,’, ROWTERMINATOR = ‘\n’ )

    STEP 4: Check the table using bellow query

    SELECT * FROM #STUDENT

    DROP TABLE #STUDENT

    and one more thing is using user defined table types in Sql Server 2008

    Check example @

    http://mpurna.blogspot.in/2012/06/using-of-user-defined-table-types-in.html

    Like

    • Your biggest risk if you’re just looping over a list to create the insert is that you may exceed the 2100-parameter limit in .Net SqlCommand creation.

      TVP works fine for to avoid this. But Bulk Insert is slightly faster in my tests. Both are much faster than comma-separated values.

      Like

  2. I just checked the execution plans for each query. Performance wise all of the queries should cost approx. the same. Only difference i saw is that the first query is a direct insert. the other two use ‘Constant Scan’,’Top’,’Compute Scalar’ (which all cost 0%) in the execution plan and then the values are inserted into the table. Also value wise (CPU time etc.) the second and the third query seem to be the same.

    Like

  3. In SQL 2008 you can use up to 1000 comma-separated clauses on the VALUES section
    e.g.
    INSERT INTO #SQLAuthority (ID, Value)
    VALUES (1, ‘First’),
    VALUES (2, ‘Second’),
    VALUES (3, ‘Third’);

    This is useful for ad-hoc SQL, but I agree with the earlier posters that normally, table-valued parameters are the way to go for inserting multiple records. If you are stuck with SQL2005 you can use XML.

    Like

    • Interesting, but there’s another limit many of us would hit first.

      If you’re executing from inside a .Net program then you’ve a limit of 2100 parameters per command. This toy example with 2 columns is okay, but in the real world you would probably blow up before you got to 1000 rows.

      Like

      • Alan, thank for the reply. OUTPUT clause will not serve my requirement, if inserts had happened from different sessions. My requirement is to get last 10 inserted records from all tables which have millions of records in each table.

        I am able to get it using IDENT_CURRENT(‘tablename’) for the tables which has IDENTITY COLUMN. Looking to get n reocrds from tables which don’t have IDENTITY COLUMN. I am just wondering is there any un-documented View, SP, Table etc. which gives me the last inserted n records.

        Like

          • I don’t wanted to add any column to the table neighter alter my indexes.
            I wanted to know will SQL server maintains any field thru that we can get the records.

            Like

  4. Not sure if this is the correct forum or subject to make a post, but I’ll give it a shot…
    I have created a view in a very complex MS-SQL db that runs an application.

    The ultimate goal is to display the data and have a few fields where users enter a few items in Sharepoint… (Example)

    CustomerName assessmentdate Invoicechecked Invoicesent
    bob jones 09/01/2012

    so above data from db is displayed (custname, assessdate) but in sharepoint users would answer yes/no etc for invoicechecked/invoicesent

    I created a view, mainly because quite a few tables had to be joined etc to pull data and I obviosuly don’t know what I am doing…

    The data in the view looks from today back 15 days. (keep that in mind) That means items will fall off the view and items in view will change. I want the items in the view to copy over to table on a regular basis. Because the view is always looking back 15 days there will be items in the view that will be duplicates or will have changed…don’t know how to deal with that either

    At the same time the Table has fields that will be null from beginning because they will be populated via Sharepoint.

    any ideas or help would be much appreciated!

    Like

  5. – Or you can Simply populate multiple values
    — from the results of a select statement
    — as long as the datatypes match

    INSERT INTO Table2
    ([col1]
    ,[col2]
    ,[col3])
    SELECT col1, col2, col3 FROM table1

    –||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
    /* FULL QUERY BELOW */
    –||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

    — Create two tables
    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[Table1](
    [col1] [int] NULL,
    [col2] [varchar](50) NULL,
    [col3] [varchar](50) NULL
    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Table2](
    [col1] [int] NULL,
    [col2] [varchar](50) NULL,
    [col3] [varchar](50) NULL
    ) ON [PRIMARY]

    GO

    — Populate the first table

    INSERT INTO Table2
    ([col1]
    ,[col2]
    ,[col3])

    select 1, ‘hi1′, ‘Dave1′
    UNION ALL
    select 2, ‘hi2′, ‘Dave2′

    GO

    — Populate Table 2 with the values of Table1
    INSERT INTO Table2
    ([col1]
    ,[col2]
    ,[col3])
    SELECT col1, col2, col3 FROM table1

    GO
    — Check the values of Table2
    SELECT
    col1
    , col2
    , col3
    FROM Table2

    GO

    (or any other valid SQL Select statement where the datatypes from the source match the datatypes of the destination.)

    Like

  6. hi, I have sql question: how can I SSIS export into one text file from several different tables: have different columns , each table has one knid of record type.
    Data in exported text file something like:
    Head record: 1,6701,T1234,101(total length20)(from table1)
    Record type 2: 2,10000000087,12/02/2012,…….(total length 300)(from table2)
    Record type 10: 10, 64538, 03/12/2012,1(total length 15)(from table 3)
    Record type 28: 28, 1000000087,,2(total length 65)(from table4)

    I tried UNION ALL and it works, but record total length like record type 1 is not 20 , instead of 300 as table2.

    Thanks.

    Like

  7. hello…. sir,
    i want to store multiple values in one tuple…
    (e.g my project is restaurant billing ) so how can i store multiple items with single bill no…. it generates multiple rows with every new item entry in database for one bill… ..
    please help me sir…..
    thank you….

    Like

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

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