Import new or update existing from text file 1

[thendrickson]

This may have been answered before, but I am trying to develop a DTS package where I import data from a tab delimited text file into SQL Server 2000.

If the record does not exist I need to append a new record to the table. If it does exist, I need to sum the new "Quantity" field with the existing and update the record.

My only idea so far is to import the data into a temp table using DTS. Then write a stored procedure to loop through that table and if found update the record and if not found then insert a new record.

Is there a better way and if so could someone point me that direction?

 

[nigelrivett]

Staging table is a good idea but the loop isn't.

update tbl
set fld = fld, fld = fld, ...
from tbl
join tmptbl
on tmptbl.pk = tbl.pk

insert tbl (fld, fld, fld, ...)
select fld, fld, fld, ...
from tmptbl
left join tbl
on tmptbl.pk = tbl.pk
where tbl.pk is null

If you have multiple entries for the same record then either take the last entry or batch or loop through the distinct entries depending on whether the dta is replaced or you have selective field updates.



======================================
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.

 

[thendrickson]

Thank you

The logic seems impeccable. I am attempting to write a stored procedure today and will repost and let you know what I do.

 

[thendrickson]

Worked very well. Thank you