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!!