Merge Statement one of the way to synch destination from source table TIP# 58

 

Problem:-  Suppose you have a source table and one destination table and you want to synch destination table from source table. means if record is already exists then you need to update and the record with latest value and insert the record if record is not exists.

Solution:-  Although there are various ways exist but here we are using a new feature came in SQL Server 2008 which is MEGE statement.

Suppose we have a  tblStudentSource table and one tblStudentDestination table

both have equal columns as shown in below figure.

both have studentId, Firstname, LastName & course column

 

if you see above figure you will find that destination table there are 2 rows while in tblstudentSource table we have 3 rows.

Now if you see in tblStudentDestination LastName of StudentID  2 is spelled wrong.

So what we have to do here. We have to Insert a new row from tblStudentSource and update existing row.

To achieve this we will use merge statement as shown in figure

Now if you see above snap  we write basic merged statement which check condition of tblSource and destination table and if condition is matched then we wrote update statement if not matched then we wrote insert statement.

MERGE tblStudentDestination AS Destination
USING tblStudentSource  AS Source
ON Source.StudentId = Destination.StudentId
WHEN MATCHED THEN
UPDATE
SET Destination.FirstName = Source.FirstName,
    Destination.LastName = Source.LastName,
    Destination.Course = Source.Course
WHEN NOT MATCHED THEN
INSERT (FirstName,LastName,Course)
VALUES (Source.FirstName,source.LastName,Source.Course);
    

Now we run above statement we got the desire result and our source and destination table is in synched.

I hope this post may help and give you a general Idea of merge statement.

Thanks!!!

RJ!!!

Import the data in main table from another table if that data not exist in main table Tip #30

 

Dear Friends,

Sometimes we get task like, we need to import data in a target table from a source table and the condition is that the data which we are going to import should not exist in target table .

To achieve this we have two ways

1) Left outer join

2) Except clause

Lets understand this by an example

Suppose in my database I have a table with name tblUser  which has few columns and few rows as shown below

Now suppose I have a table with name @tblToBeImport which has some data related to Username & Password.

In this table some rows having same username & password which already exist in our source table as shown in figure

Now our motive is to first fetch those data from tblToBeImport (source table) which need to be insert in tblUser table (target table)

It means if username & password already exists in tbluser then we will not insert it else we will create a new entry.

Here , We are using below left outer join to find the rows which not exists in tblUser but exists in tblToBeImport

In below snap you will find there we applied Left outer join to achieve this

SELECT tmp.username,Tmp.uPassword
FROM @tblToBeImport tmp
LEFT OUTER JOIN tblUser u ON u.UserName = tmp.username
             AND u.Password = tmp.uPassword
WHERE u.UserId IS NULL

Now, same can be achieve using EXCEPT clause  as shown in below figure

 
SELECT username,
       uPassword
FROM @tblToBeImport
EXCEPT
SELECT[UserName]
      ,[Password]
FROM [dbo].[tblUser]       

Now my personal experience Left outer join is very slow on large table comparatively EXCEPT clause.

I prefer to user Except clause .

Enjoy!!

How to Insert multiple values using a single statement tip #26

 

It is very interesting how to insert multiple values with a single statement.

Lets understand this with the help of below example.

Suppose you have a Country table and you want to insert seed (initial values) then you can write those statement like below

 

Now same values can be inserted with another one of the interesting way

So in the above snap you have seen that we can use either Values statement or UNION to insert multiple values with single Insert statement.

Enjoy !!!

Thanks

Raj