Row Constructors in SQL Server 2008
We all know the fact that ISUD transactions are the Crux of RDBMS/T-SQL and also how well and often the LOB(Line of Business) relies on ISUD and implements it in their business logic thereby performing thousands and millions of transactions a day(Especially, inserts and updates).
Row Constructors is a really cool handy feature that comes to play with the ‘I’ part of ISUD transactions. That is, the INSERT transactions .
I personally feel that Row Constructors are a relief to single ton manual inserts. They definitely help boost the efficiency of I-Transactions (INSERT).
If you really want to second my statement … then I bet you should have a quick look at the case study below.
The concept ‘Row Constructors in SQL Server 2008’ basically deals with the crux of multiple inserts performed at one shot. To get a brief picture of why we need multiple inserts and the advantage it has we firstly need to understand all possible ways of INSERTING Data into a Table
The possible ways of conducting inserts to a table/ Inserting Data into a Table:
CASE (i) : INSERT INTO
By employing ‘INSERT INTO’ we can conduct Single ton Inserts at one shot and this could be repeated multiple times (depending on the data to be entered).
Look at the following examples.
Ex: Assume that we have an existing table named Car and we need to Insert some data specific to cars into it.
CREATE TABLE Car
(
CarID varchar(10)
,Name varchar(40)
,Color varchar(20)
,Brand varchar(20)
)
INSERT INTO Car VALUES('AP10J4195','Swift','FerrariRed','Maruthi');
INSERT INTO Car VALUES('AP10P4184','800','White','Maruthi');
INSERT INTO Car VALUES('AP10K2340','CityZX','Grey','Honda');
INSERT INTO Car VALUES('AP10K2218','CityZX','BlackStatement','Honda');
INSERT INTO Car VALUES('AP10K2000','Mercedesc200','BlackStatement','Benz');
The first time I write my INSERT INTO VALUES… statement. And, then I I do a Copy paste several times and refill values and then do a Ctrl+E.. . I need to recheck my data & statements here.
Case(ii) : INSERT INTO USING SELECT
Similar to Case (i) we conduct single ton inserts multiple times.
We end up writing a chunk of code L.
Here again, I do an Alt+F1 : Copy- Paste Columns and build my INSERT Query
Example: Assume that we have an existing table called Car and we insert need to some data from Car into a new table ‘Vehicle’.
(Create the Car Table same as above)
CREATE TABLE Car
(
CarID varchar(10)
,Name varchar(40)
,Color varchar(20)
,Brand varchar(20)
)
INSERT INTO VEHICLE
(
Name
,Color
,Brand
)
SELECT Name
, Color
, Brand
FROM [dbo].Car
WHERE Brand = 'Honda'
SELECT * FROM Vehicle
Case(iii) : SELECT INTO
This statement is used when a table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
I don’t use this much may be because it doesn’t cover all possible cases . Generally, go for Case(ii).
Examples:
SELECT Name
,Color
,Brand
INTO CarOfTheYear
FROM [dbo].Car
WHERE Name = 'Swift'
SELECT * FROM CarOfTheYear
Case (iv) : UNION ALL
This has been a handy statement that developers generally use. Especially, as part of Data Insertion Scripts.
Regarding performance there is not much difference. If there is performance difference it does not matter as this is generally used one time insert script.
There is no need to do a copy - paste of Chunk of code and refill values (though people do it ). You can enter values employing subsequent SELECT’s and have focus on your tasks rather than re-checking it again and save time.
INSERT INTO VEHICLE
(
Name
,Color
,Brand
)
SELECT 'Activa','BlackStatement','Honda'
UNION ALL
SELECT 'Pulsar', 'BlackStatement','Bajaj'
UNION ALL
SELECT 'I10','Silver','Hyundai'
UNION ALL
SELECT 'Swift','CementGrey','Maruthi'
GO