Copying Data

Rene writes "Good evening. This is my first time using sqlteam for answers and I'm hoping I can get some much needed direction.

Basically what I am trying to accomplish is taking records from a temporary source table to a permanent table. Here is what the tables look like (oversimplistic version)

tblTemp

ID
Value1
Imported
FailedReason


tblPerm

ID
Value1


Basically I would like to take the values from tblTemp and INSERT them into tblPerm. The catch is that values in tbltemp might violate primary key constraint because of duplicate values in the ID field. The value1 field is required in tblPerm and it might contain a null value on tbltemp causing the insert statement to fail.

What I would like the end result to be is that any records which are INSERTED from tbltemp to tblperm are flagged with a value of imported=1 on the tbltemp table. Any records which fail should then be flagged as imported=0 and failedreason=reason for failing.

I am trying the following to start with but am not sure if I am steering in the right direction or not. The process should be as automated as possible, perhaps part of a scheduled dts package or likewise since new data will be inserted in the tbltemp table on a weekly basis.


set rowcount 1

update tblsource set imported = 0 where imported is null

insert into tbldest (col1, col2)
select top 1 col1, col2 from tblsource where imported = 0 and notimportreason is null

IF @@Error <> 0
GOTO ErrorHandler

UPDATE tblsource SET imported = 1 where imported = 0 and notimportreason is null

ErrorHandler:

update tblsource SET notimportreason = @@error where imported = 0 and notimportreason is null


RETURN"