April 11, 2002 at 2:03 pm
DTS Package:
My first connection is a source text file
My second connection is a temp table in my DB called TempLoad
After transforming the data I would like to perform the following.
Transfer the data from TempLoad to my SourceLoad table. However if there is any records in my TempLoad table that match my SourceLoad table I want to overwrite them. This is because where there are duplicates that mean that the records have been updated. For instance:
Record from TempLoad
ID Name Address State ZIP Phone
1 John Smith 555 East Main NY 14580 555-1212
Etc…
Record from SourceLoad
ID Name Address State ZIP Phone
1 John Smith 555 East Main NY 14580 555-4567
Etc…
Notice the phone number has changed. In some cases it may be any part of the record that may change other than the ID this will obviously be the same. So in these cases the record must be dropped and the new record must be placed in reflecting the changes.
If there are no matching records coming from the SourceLoad table then they are assumed to be new records and must simply be added as a new record into the SourceLoad table
Any help or source information will help. Thanks People!!!!
April 11, 2002 at 3:34 pm
You can use a data driven query (I believe ) to do this, but it's too complex in my opinion.
I usually do this with a tempload table that is strictly for the load. I load into this table.
Then I do an update to my data table with matching on some PK and change any data.
I then delete with the same matching from the temp table. This leaves only new records.
I insert these into my data table.
If this is from multiple sources and each source could have duplicate data, then I would de-dup my temp table before matching with my data table.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 11, 2002 at 8:04 pm
You woudn't happen to have any examples of this would ya? Thanks. I only have one data source. (text file)
April 15, 2002 at 3:24 am
I do this a LOT. This should work for you
Do the update first
UPDATE f
SET
name = s.name,
address = s.address,
state = s.state,
zip = s.zip,
phone = s.phone
FROM tempload f
JOIN sourceload s ON (f.id = s.id)
Then you can do an insert with
INSERT sourceload(Name Address State ZIP Phone)
SELECT DISTINCT
ID,
Name,
Address,
State,
ZIP,
Phone
FROM tempload a
WHERE NOT EXISTS(SELECT
b.id FROM sourceload b
WHERE a.id = b.id)
Alternatively you could first delete all the records in Sourceload that match any in tempload, then do a straight insert with the correlated subquery.
Let me know how it goes
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply