Use DTS to write data from SQL to Dbase

[christer102]

Trying to insert data from SQL to Dbase using DTS. The problem is that I have it just "cloning" the data, only inserting data where the record already exists (so I end up having two record in the Dbase table), and it is ignoring any new records that exists in the SQL table. Obviously I need an insert statement, I have tried the datadriven query, which is telling me no query spefication returned by transform status. Can anyone point me in the right direction how to do this? I need to have insert new records from SQL, and then secondly update any existing records from SQL.

 

[AlexCuse]

First run your update query for everything that already exists in destination (use an inner join).

Then, to insert only the new records:

select a.*
from SourceTable a
left join DestinationTable b
on a.PrimaryKey = b.PrimaryKey
and a.OtherIdentifier = b.OtherIdentifier
where b.PrimaryKey is null

This is easy if you have a linked server set up (don't know if this is even possible for dBase) but if you don't, you will have to bring everything across into a staging table and then perform your updates and inserts from this staging table.

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom