A day with .Net

My day to day experince in .net

Archive for June, 2017

Insert values to a table with MERGE and keeping the order of insertion SQL Server

Posted by vivekcek on June 28, 2017

In some cases you may want to insert some values into the production database during a new release.
What we normally do is sharing some data insert script to the DBA.
What will happen when this DBA people run your script multiple times? Duplicates!!! oops!! application down!!

So to avoid this we need to add condition for duplicate checking in our query.
This can be achieved by MERGE statement.

Try this example.

1. Create a table

CREATE TABLE UserInfo(Id INT,Name VARCHAR(50), Email VARCHAR(100));

2. Insert one record.

INSERT INTO UserInfo VALUES(1,'Vivek','vivek@vivek.com')

3. Now prepare the data need to insert. Here we are using the SELECT and UNION ALL.
UNION ALL will preserve the order of insertion (Try with UNION only then you can see the difference)

SELECT 1 AS Id,'Vivek' AS Name, 'vivek@vivek.com' AS Email
UNION ALL
SELECT 2 AS Id,'Ramu' AS Name, 'ramu@ramu.com' AS Email
UNION ALL
SELECT 3 AS Id,'Jay' AS Name, 'Jay@ramu.com' AS Email

4. Now write the MERGE statement as below and execute. You can see only 2 records got inserted and they are in order.

MERGE INTO UserInfo AS UserInfoTarget
USING(
SELECT 1 AS Id,'Vivek' AS Name, 'vivek@vivek.com' AS Email
UNION ALL
SELECT 2 AS Id,'Ramu' AS Name, 'ramu@ramu.com' AS Email
UNION ALL
SELECT 3 AS Id,'Jay' AS Name, 'Jay@ramu.com' AS Email
)AS Source
ON (UserInfoTarget.Id=Source.Id AND UserInfoTarget.Name=Source.Name AND UserInfoTarget.Email=Source.Email)
WHEN NOT MATCHED THEN
INSERT VALUES(Source.Id,Source.Name,Source.Email);

Full code.

Advertisements

Posted in SQL | Tagged: , , , | Leave a Comment »