sql server dts: Load if not exists -- sql server dts

RE: Load if not exists

Matt
7/13/2006 6:10:02 PM

The easiest way to do this would probably be with transform data task with a
source query like the following:
SELECT SourceTable.*
FROM SourceTable
LEFT JOIN DestinationTable
ON SourceTable.PrimaryKey = DestinationTable.PrimaryKey
WHERE Destinationtable.PrimaryKey IS NULL

This query will return all records from your SourceTable that do not have
corresponding records in your destination table. Hope this helps!
[quoted text, click to view]

RE: Load if not exists

Chris
7/14/2006 10:05:02 AM

Thanks Matt.

Sorry - I neglected to mention I was looking for a solution with SSIS.

My technique is to send the data to a staging dest table, then call a sproc
that does this exact left join technique on the dest server once the data is
local.

What would be a common solution? Somehow a lookup first, if found ignore, of
not then send to dest table. I prefer this, then I don't have to manage
staging tables.

Thanks,
chris

[quoted text, click to view]

Re: Load if not exists

Allan Mitchell
7/15/2006 2:42:57 AM

Hello Chris,

What about using the Lookup transform?

You can also use the merge Join transform if you find yourself with low memory
or large reference tables

Allan

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.