SQL Tips by Namwar Rizvi

August 31, 2007

Multiple Inserts in one statement – Row Constructor in SQL Server 2008

Filed under: Information,New Features,SQL Server 2008 — namwar @ 11:23 PM

Don’t you feel boring sometimes when you have to write an script to insert multiple rows in a table and you do not have any other choice except writing multiple insert statements? In one of my previous posts I showed a way to insert multiple rows by using UNION ALL but wait! there is another way available in SQL Server 2008 called Row Constructor which is an ANSI terminology for pseudo table of rows.
It is actually a way to provide a set of row values in one statement. Following example will help you understand better. Please note that this example can work only on SQL Server 2008 or above:

–Switch to tempdb
Use tempdb
Go

–Create a test table in temdb
Create table tblCountries (id int, country varchar(50)
Go

–Inserting multiple values
Insert into tblCountries (id,country)
Values
(1,‘USA’), –Row 1
(2,‘UK’), –Row 2
(3,‘France’) –Row 3

–Now select, you will get 3 rows
Select * from tblCountries

Advertisements

9 Comments »

  1. I don’t know if I should laugh or cry? I wish I had this now. Oh well – perhaps I’ll just wait 5 years while everyone gets around to deploying 2008 or better, then perhaps I’ll amend all my scripts.

    Comment by Adam — October 16, 2007 @ 6:40 PM | Reply

  2. This has also been in mysql since 2001.

    Comment by voidlogic — April 29, 2009 @ 7:27 PM | Reply

  3. Guys wht about hte performance gain , i have seen no peerformance gain , that is the reason iam not amending my old scripts .well it helps in writing few lines copred to previous versions.Please let me know if any of you have done performance analysis.on sql 2008.

    Comment by sandeep — June 15, 2009 @ 6:47 AM | Reply

  4. This is very similar to MySql and in MySql when you use this multiple insert it is way faster than several inserts.

    Comment by chfajardo — September 12, 2010 @ 11:55 AM | Reply

  5. Yo lo resolvĂ­ de esta forma:

    INSERT INTO NombreTabla
    SELECT ‘ColumnaUno 1′,’ColumnaDos 1’, ‘Columna n 1’
    UNION SELECT ‘ColumnaUno 2′,’ColumnaDos 2’, ‘Columna n 2’
    UNION SELECT ‘ColumnaUno 3′,’ColumnaDos 3’, ‘Columna n 3’
    UNION SELECT ‘ColumnaUno 4′,’ColumnaDos 4’, ‘Columna n 4’
    GO

    Comment by Gregorio Marciano — May 20, 2011 @ 6:21 PM | Reply

  6. I tried the same but it isnt working in SQL Server 2008 R2. Can you get me a way around this?

    Comment by Sagar — October 4, 2011 @ 1:10 PM | Reply

    • Create table tblCountries
      (
      id int,
      country varchar(50)
      )

      Insert into tblCountries (id,country) Values
      (1,’USA’),
      (2,’UK’),
      (3,’France’)

      you ca remove (Id,Country) worlds also it will work

      Comment by Jojin Jose — March 16, 2013 @ 5:10 AM | Reply

  7. 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.Just need to show POC.
    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 7, 2011 @ 8:30 PM | Reply

  8. Hi,

    What all can we include in SSAS Cube Design documentation

    I have included the data model,facts,dimensions,surrogate keys,Hierarchies and levels within hierarchies.
    Reports that we can pull from the cube,Proactive caching.

    What else can we include.
    I’m confused.Please help me out.

    Thanks in Advance,
    Samyuktha

    Comment by Samyuktha — December 19, 2011 @ 5:14 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

Blog at WordPress.com.

%d bloggers like this: