SQL Tip of the Day

  • Artigo

HAd this passed to me from the SQL Tips DL here at the 'soft and decided to repost it here to 1. remember about it and 2. share the tidbit

INSERT MULTIPLE RECORDS WITH TABLE VALUE CONSTRUCTOR

Author: Saleem Hakani (Microsoft Corporation)

 

Wouldn’t it be nice to have the ability to insert multiple rows of data using one Insert statement? Every time you create a table and have to insert rows manually, you will need to repeat “Insert Into <Tables> values..”

 

SQL Server 2008 now provides the ability to insert multiple rows of data through one Insert statement. This makes the entire statement part of one single DML (Data Manipulation Language) statement. This is called Table Value Constructor and it can be specified in the VALUES clause of the INSERT statement.

 

LETS TAKE AN EXAMPLE:

You want to create an ITEM table with ITEMNO and ITEMNAME and you would like to insert 5 records in it.

 

SAMPLE ITEM TABLE CREATION

 

Create Table ITEM

(

ITEMNO INT IDENTITY(1,1),

ITEMNAME VARCHAR(50)

)

 

TRADITIONAL INSERT STATEMENT FOR INSERTING MULTIPLE ROWS

In earlier versions of SQL Server, you would have to repeat the insert statements for as many number of times you had to insert the rows.

 

INSERT INTO ITEM VALUES ('MANGO')

INSERT INTO ITEM VALUES ('APPLE')

INSERT INTO ITEM VALUES ('BANANA')

INSERT INTO ITEM VALUES ('GRAPES')

INSERT INTO ITEM VALUES ('PLUMS')

 

INSERTING ROWS WITH TABLE VALUE CONSTRUCTOR

With the introduction to Table Value Constructor, you no longer have to use the insert statements multiple times. One Insert statement will do it.

 

INSERT INTO ITEM VALUES

('MANGO'),

('APPLE'),

('BANANA'),

('GRAPES'),

('PLUMS')

 

 

As you can see from the above INSERT statement, you inserted multiple records using one single INSERT statement.

 

Keep in mind: You can only insert up to 1000 records using Table Value Constructor. However, if you have more than 1000 records, you should look in to using BCP or BULK INSERT.

 

BONUS TIP: GO BATCH REPLICATOR

Did you know: ‘GO’ statement is not a T-SQL statement? It is a command recognized ONLY by the client utilities like (SQLCMD, OSQL & SQL Server Management Studio, etc.) Whenever the client utilities encounter ‘GO’ statement, it signals the SQL Server engine that the command has reached the end of statement.

 

‘GO’ command is NEVER sent to SQL Server database engine. Also, any SQL Server user regardless of the permissions can execute ‘GO’ statement using any SQL Server client utility.  

 

With the latest edition of SQL Server client tools, ‘GO’ now supports an additional parameter <count> that allows the batch to be executed for that many number of times.

 

This means if you have an insert statement as follows:

 

INSERT INTO PRODUCTS VALUES (1, 'SQL SERVER 2008', 'MSSOLVE TEAM')

GO

This will insert only one record to the PRODUCTS table.

 

However, if you don’t care about data redundancy and want to insert the same row 10 times, you no longer need to execute the above statement 10 times. Instead, you will simply pass 10 as a parameter to GO command as shown in the below example:

 

INSERT INTO PRODUCTS VALUES (1, 'SQL SERVER 2008', 'MSSOLVE TEAM')

GO 10

 

The above ‘GO’ command will take care of inserting the same row 10 times in the PRODUCTS table.

Comments

  • Anonymous
    June 26, 2011
    Table value constructors can be used to create tables on the fly not only within INSERT(s) but also in the other CRUD statements. The tables thus created can be used in joins and quite powerful functionality can be derived from this. Here are a few basic examples: sql-troubles.blogspot.com/.../table-value-constructors-at-work.html.

  • Anonymous
    August 01, 2011
    The comment has been removed