|
|
Resolution to SQL Server problems. From the front line.
OK so you have been using Databases for a long time.. You know SQL Server, well I have been trying to work out how many ways there are to write a simple Insert SQL Statement. I think there could be around 6 different ways (4 main ways with 2 other flavours), but I am open to other suggestions.
Firstly we need to decide what Data we want to Insert, so I have created a test table and with each method I want to insert 3 lines of data.
The test table DDL is below and I just want to add 3 rows, each consisting of an ID and a piece of text
CREATE TABLE dbo.InsertTest
(
ID INT NOT NULL,
TextString NVARCHAR (100) NOT NULL,
PRIMARY KEY CLUSTERED
(
ID
)
);
Anyway, here are my 6 different ways... These are the 4 main 'different' ways I have come across - Though you may decied Method 2 and 3 are flavours of the same technique
Method 1 Classic Insert...
INSERT INTO dbo.InsertTest (ID, TextString) VALUES (1 , 'Value: 000001');
INSERT INTO dbo.InsertTest (ID, TextString) VALUES (2 , 'Value: 000002');
INSERT INTO dbo.InsertTest (ID, TextString) VALUES (3 , 'Value: 000003');
Method 2 Insert with row constructor
This allows you to specify multiple rows in a single INSERT statement.
INSERT INTO dbo.InsertTest (ID, TextString) VALUES
( 4 , 'Value: 000004'),
( 5 , 'Value: 000005'),
( 6 , 'Value: 000006');
Method 3 Insert with Select Values Clause
INSERT INTO dbo.InsertTest (ID, TextString)
SELECT T.ID, T.TextString
FROM
(
VALUES
( 7 , 'Value: 000007'),
( 8 , 'Value: 000008'),
( 9 , 'Value: 000009')
) AS T (ID, TextString);
Method 4 Merge Statement
MERGE dbo.InsertTest AS TARGET
USING (SELECT T.ID, T.TextString
FROM
(
VALUES
(10 , 'Value: 000010'),
(11 , 'Value: 000011'),
(12 , 'Value: 000012')
) AS T (ID, TextString)) AS SOURCE
ON (TARGET.ID = SOURCE.ID)
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, TextString)
Those were the 4 main ways, here are my 2 flavours…
Method 5 Classic Insert Statement with CTE
; WITH CTE_Insert (ID, TextString)
AS
(
SELECT T.ID, T.TextString
FROM
(
VALUES
(13 , 'Value: 000013'),
(14 , 'Value: 000014'),
(15 , 'Value: 000015')
) AS T (ID, TextString)
)
INSERT INTO dbo.InsertTest (ID, TextString)
SELECT ID, TextString
FROM CTE_Insert;
Method 6 Merge Statement with CTE
;WITH CTE_Insert (ID, TextString)
AS
(
SELECT T.ID, T.TextString
FROM
(
VALUES
(16 , 'Value: 000016'),
(17 , 'Value: 000017'),
(18 , 'Value: 000018')
) AS T (ID, TextString)
)
MERGE dbo.InsertTest AS TARGET
USING (SELECT ID, TextString FROM CTE_Insert) AS SOURCE
ON (TARGET.ID = SOURCE.ID)
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, TextString)
VALUES (SOURCE.ID, SOURCE.TextString);
And to prove they all work.....
SELECT COUNT(*) FROM dbo.InsertTest;
18
In my following posts I will show you what is the fastest method and a Gotcha with one of the above techniques
See also:
http://consultingblogs.emc.com/paulmcmillan/archive/2011/07/06/so-now-you-can-write-an-insert-statement-6-different-ways-why-do-you-need-to-know-this-and-why-is-merge-the-best.aspx
http://consultingblogs.emc.com/paulmcmillan/archive/2011/07/06/a-gotcha-with-the-insert-statement-row-constructor.aspx
http://consultingblogs.emc.com/paulmcmillan/archive/2011/07/06/so-you-may-have-many-different-ways-of-inserting-data-but-which-is-the-fastest.aspx
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
|
|
|