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

tblUserStructure

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

importtable

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

LeftOuterjoin

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

Except

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

Now we have selected data which not exists in tbluser but exists in @tblToBeImport  we can easily insert it in tbluser table.

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

I prefer to user Except clause .

Enjoy!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s