Hi
What do you do if the existing record has been updated? Even if there is no
primary key there should (hopefully!) be a unique way (set of columns) to
identify them, this should be the PK in the SQL Server database.
One method is to load into a staging table then work from there. If you
drive the transformation using a SQL command in the form
SELECT S.Col1, S.Col2, ...
FROM StageTable S
WHERE NOT EXISTS ( SELECT 1 FROM DestinationTable D WHERE D.PK = S.PK )
You will not insert the existing rows. You can also use an additional SQL
Command step before the insert to update existing rows.
UPDATE D
SET Col1 = S.Col1,
Col2 = S.Col2
FROM DestinationTable D
JOIN StageTable S ON D.PK = S.PK
WHERE D.Col1 <> S.Col1
OR D.Col2 <> S.Col2
John
"Tony" <tc*******@rihousing.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
Hi all,
I need to export tables out of a Pervasive DB and into SQL Server 2K. I
have set up a DTS Package to do this when a user visits a web page
(which will then allow them to view a up to date report using MS
Reporting Services).
Currently my DTS package checks to see if the table exists in SQL
Server and then drops it, creates it, and then does the import of data
from Pervasive.
I am wondering if there is a way to get only the new records? There are
currently no PK defined (Pervasive does not make use of them). However,
I noticed that the DTS package can assign PK.
Does anyone have any advice/code snippets?
I greatly appreciate your help.
Thanks,
Tony