SQL Tips by Namwar Rizvi

June 16, 2007

Inserting multiple rows with a single INSERT statement

Filed under: Query,tips,TSQL — namwar @ 10:32 PM

Sometimes you want to insert multiple rows but do not want to write those multiple INSERT statements, right? So here is the quick tip. You can do so by using UNION operator and INSERT INTO. The best part is it will automatically be in a single transaction, so you don’t have to explicitly manage your own transaction. Here is the sample code to do this:

–Creating a sample table for this example
Create table testTable
( id int not null,
city varchar(50)

)

—Inserting 3 rows by a single Insert statements
Insert into testTable (id,city)
Select 1,‘New york’
Union
Select 2,‘London’
Union
Select 3,‘Paris’

–Display table contents
Select * from testTable

Advertisements

5 Comments »

  1. It is better to use UNION ALL than UNION for performance.
    Read the earlier written article :
    SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL

    Regards,

    Comment by Pinal Dave — June 17, 2007 @ 11:36 PM | Reply

  2. i m not agree with solution

    Comment by vinay — September 29, 2008 @ 12:45 PM | Reply

  3. i want to insert /update in database on selected cell with the value of textbox.

    Comment by vinay kumar kasera — September 29, 2008 @ 12:47 PM | Reply

  4. Yes Pinal, you are right. Union All is faster than Union. Thanks.

    Comment by namwar — September 30, 2008 @ 8:51 PM | Reply

  5. Hi,

    I got a task to do.I need to insert 400,000 rows into Adventure Works DB table inorder to see how Proactive caching works in different scenarios.
    My question is how to insert 400,000 rows at a time in a table.
    Can you let me know the script to generate it.

    Please do help me out.

    Thanks in Advance.

    Comment by Samyuktha — December 8, 2011 @ 10:16 PM | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: