dts import advice please

[sixtoedsloth]

HI there,


i appear to be duplicating records as i import them using dts. what is the best way to stop this. i cant drop the table or truncate it as people are locking it.

regards,

Russ

 

[nigelrivett]

Depends on what you are doing (if you are importing text files then dts is probably not a good choice).

Maybe import into a staging table then use an SP to decide which recs to copy to the production table.

see

http://www.nigelrivett.net/ImportTextFiles.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[sixtoedsloth]

Well. i suppose more detail is in order ;->


connection1 = connx odbc driver to powerflex data
connection2 = sql server

need to do the following:-
select * from connection1.scanbox where scanned_date = curdate() and put the results in connection2.scanbox

scanbox contains 500,000+ records
scanned_date is date in dd/mm/yyyy format, is indexed on scanned_date and record_number and
returns between 0 and 7000 records depending on how far through the day we are. i have no concept of time, only date. with access i can do a select query that gets the correct recordset and takes upto 15 seconds to return.

so every minute i wish to get the records with todays date and insert them into the sql table. however, i know that some of the records will be duplicated in the sql table.
i need to use record_number. if it exists i want to update the record, if not i want to add the record.

if i truncate the table on sql server and do a dts import of the new records, it takes nearly a minute to do the insert. which is too slow!

Hope this gives you a better idea of the situation.

Cheers,


Russ