Sharing of thoughts and information is what blogging is all about. This way we can learn from each other. Post A Comment!These postings are provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
Anthony Bartolo Twitter | LinkedIn
Pierre Roman Twitter | LinkedIn
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 and up provide 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) operation. 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),
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
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: The ‘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 a ‘GO’ statement, the ‘GO’ signals the SQL Server engine that the command has reached the end of statement.
The ‘GO’ command is NEVER sent to the SQL Server database engine. Also, any SQL Server user, regardless of 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')
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:
The above ‘GO’ command will take care of inserting the same row 10 times in the PRODUCTS table.