Insert Multiple Records Using One Insert Statement (Row Constructor)

Posted: November 20, 2011 in SQL Server 2008

To insert multiple records using one insert statement, you can do the following:

-- Create table
CREATE TABLE dbo.MyTable 
            (my_value VARCHAR(20) NOT NULL,
             my_rec   INT         NOT NULL);
	          
-- Insert multipe records into table - prior to sql server 2008 (option 1)
INSERT INTO dbo.MyTable 
           (my_value, 
            my_rec)
VALUES     ('One',
            1);	          
INSERT INTO dbo.MyTable 
           (my_value, 
            my_rec)
VALUES     ('Two',
            2);
INSERT INTO dbo.MyTable 
           (my_value, 
            my_rec)
VALUES     ('Three',
            3);	    

-- Insert multipe records into table - prior to sql server 2008 (option 2)
INSERT INTO dbo.MyTable 
           (my_value, 
            my_rec)
SELECT      'Four', 4
UNION ALL
SELECT      'Five', 5
UNION ALL
SELECT      'Six', 6;

-- Insert multiple records into table - sql server 2008
INSERT INTO dbo.MyTable 
           (my_value, 
            my_rec)
VALUES     ('Seven', 7),
           ('Eight', 8),
           ('Nine', 9)

-- Select records
SELECT * FROM dbo.MyTable;
Advertisements

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