Home > Insert, SQL Queries, SQL Server, T-SQL > Use single “INSERT” statement for multiple value inserts in SQL server 2008

Use single “INSERT” statement for multiple value inserts in SQL server 2008

In Sql Server 2005 or earlier , to insert each record into a table , you had to isuue one insert statement. One of the new programmability enhancements in the Database Engine introduced in SQL Server 2008 for Transact-SQL is the row constructor which consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma.  With the row constructor, you can now insert multiple values using a single INSERT statement.  

Example:

INSERT INTO [dbo].[TestTable] VALUES
('1', 'Simon'),  
('2', 'Mark'),  
('3', 'Peter')

One of the issue with this approach is that the number of records in a single insert is limited to 1000 records. You might get an error if you try to insert more than 1000 records.

Advertisements
  1. Liliana Piñeros
    August 4, 2010 at 9:41 pm

    Hello,

    We use INSERT SELECT clause to insert data in empty table, i have a question: When does the database begin to insert data? The database firts resolves the select and then insert?
    Please help me.
    Best regards.

  1. No trackbacks yet.

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

%d bloggers like this: