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