Thursday, February 16, 2012

Append only unique records in SQL table

I have a stored procedure that appends data from a temp table to a destination table. The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates.

The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table?

Thanks in advance.
SMc

Lets say your destination table is called Dest and you temporary table is called Temp.

Both tables have two fields, A and B where A is the one that needs to be unique in Dest, but it's not necesarily unique in Temp (or Temp contains rows with a value of A that already exists in Dest).

insertinto Dest

select Temp.A, Temp.Bfrom Temp

where Temp.Anotin(select Dest.Afrom Dest)

|||

I was just fighting with similar, but a lot of simpler problem. Maybe these posts will help.

http://forums.asp.net/t/1185782.aspx

And IF NOT EXISTS(SELECT * FROM table WHERE .....

Hope it helps

Leif

No comments:

Post a Comment