Jack Corbett (1/19/2009)
If you are using SSIS then the lookup component is the current method to do this. If the databases are on the same server you could get better performance by doing a set-based insert using either a LEFT JOIN or WHERE NOT EXISTS query like this:
Insert Into destination
Select
S.columns
From
source S Left Join
destination D On
S.primary_key = D.primary_key
Where
D.primary_key Is Null
Or
Insert Into destination
Select
S.columns
From
source S
Where
Not Exists (Select 1 From destination D
Where S.primary_key = D.primary_key)
There is some evidence that option 2 performs a little better.
i used this tip. and i find it realy good to implement.