DTS new records only 1

[jgurgen]

I have set up a DTS to copy data from one database to another. It ran fine the first time but the second time im getting primary key constraint errors. How do i set up a way to copy only records that dont exist in the new table? This is my first time using DTS so please bare with me. Im using sql 2000 with enterprise manager.

 

[AlexCuse]

Step 1 - import first to a staging table (same database as destination)

Step 2 - change your source query for load to final table to something like this:

select a.[ID], a.field1, a.field2....
from [STAGINGTABLE] a left join [FINALTABLE] b
on a.[ID] = b.[ID]
where b.[ID] is null

You can keep your transformation the same, but will want to make sure you use the query for source rather than staging table as a whole.

Step 3 - Truncate staging table

Hope this helps,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[jgurgen]

im adding a bit coloumn to this table that doesnt exist in the original, do i need to also create this coloumn in the stagetable or just in the finaltable?

also im orignally exporting the data should i delete that and create an import and do both as imports? (whats the difference in import and export because they look like they give the same options)

 

[AlexCuse]

What is your initial data source? I'm confused about your import/export comment.

Adding the bit field will not be a problem. Depending how you need to work with this field it may be nice to add it to the staging table, but it won't be a problem if you don't.



Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[jgurgen]

one more thing, my ID is loanid and historycounter not an incrementing number. So i am assuming my code will look like this?

Select s.LoanID, s.HistoryCounter, s.TranDate, s.TranCode, s.TranAmt
From [iProf_StageTable] s Left Join [iProf_History] d on s.LoanID = d.LoanID and s.HistoryCounter = d.HistoryCounter
WHERE d.LoanID is null and d.HistoryCounter is null

 

[jgurgen]

my initial datasource is a database created by a program we are using that keeps a history of transactions. In the history table there are 5 columns that i need and i need to add a checkbox to them on the seperate database.

I have set up the the stage table and finaltable with the DTS from above to copy from the stage to final.

 

[AlexCuse]

Sounds like you are good to go.

Is the source database access/sql/other type? If it's SQL it would be possible to do this all in one step, but I think the staging table option gives more flexibility.

Let me know how things work out for you,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[jgurgen]

yeah its all sql, it seems to be running fine no errors anymore. Gonna add some stuff to the database now to make sure it updates correctly. Thanks a lot for your help.

 

[jgurgen]

A little problem now.

My stage table keeps copying data over and now i have 90 million records and duplicates galore. How can i change it to set it up where only copy records that don't exist or are different?

 

[AlexCuse]

sounds like you missed this step:

Step 3 - Truncate staging table

This will clear out all data from the staging table. That way each time you load it will only contain that specific load's data (rather than a history of all loads).

The command for this (in a sql task) is

truncate table tablename

A wise man once said
"The only thing normal about database guys is their tables".

 

[jgurgen]

can i put this in the DTS or do i have to create a seperate action for this to run?

 

[AlexCuse]

You just need to add a sql task to your workflow (I would put it right at the start). This is the icon in DTS designer that looks like a little cylinder with red arrows in a circle on it.

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[jgurgen]

perfect, thank you so much
i was wondering why there were 90 million records instead of 17000