SQL Scrapbook

Short and simple SQL Server posts

Posts Tagged ‘SQL Server 2008

Row Constructor

leave a comment »

I just discovered a very cool feature in sql server 2008 which i always wanted. With this feature called the row value constructor or the table value constructor you no longer have to write multipe insert statements or use the
INSERT INTO
<TableName> SELECT <ColumnList> UNION ALL block to insert values into a table.

Row constructors/Table value constructors let you insert multiple rows into a table with single insert statement.

IF OBJECT_ID('#Employee') IS NOT NULL
DROP TABLE #Employee --Drop the table if it exists
GO

CREATE TABLE #Employee (EmpID INT, EmpName VARCHAR(50)) -- Create the table 

INSERT	#Employee     -- This inserts 5 rows
VALUES	(1,'JBauer'),
	(2,'DPalmer'),
	(3,'NCaffrey'),
	(4,'PCollingwood'),
	(5,'PFernandeso')
The above statement inserted 5 rows into the #Employee table. The row values have to specified in the VALUES clause of the insert statement and they should be in braces separated by a comma.
You can see the result by executing the below select statement
SELECT	EmpID,
	EmpName
FROM    #Employee

The row constructors can also be used in a derived table:

SELECT  EmpID,EmpName
FROM	(VALUES(1,'JBauer'),
        (2,'DPalmer'),
        (3,'NCaffrey'),
        (4,'PCollingwood'),
        (5,'PFernandeso')) AS DerTable(EmpID,EmpName)

Any constraints on the tables like the IDENTITY,NOT NULL or the DEFAULT can’t be overwritten when inserting using the row constructors. An explict value
can’t be insterted into an identity column and a NULL value can’t be inserted into a column with a NOT NUL constraint defined on it.

Row Constructor with NOT NULL and DEFAULT”

IF OBJECT_ID('#Employee1') IS NOT NULL

DROP TABLE #Employee1
CREATE TABLE #Employee1 (EmpID INT, EmpName VARCHAR(50) NOT NULL DEFAULT 'N/A')

INSERT #Employee1
VALUES	(1,'JBauer'),
	(2,'DPalmer'),
	(3,'NCaffrey'),
	(4,'PFernandeso'),
	(5,DEFAULT)

SELECT	EmpID,EmpName
FROM	#Employee1

While i don’t understand the limit of 1000 rows that can be inserted using this approach, there are some more meaningful limitaions like
1) The number of columns for each row in a table value constructor must be the same
2) The data types of the corresponding columns should be of the same domain.

Advertisements

Written by sqlscrapbook

July 20, 2010 at 8:13 pm