avtar sodhi
avtar sodhi

Reputation: 1

Continue inserting data in tables skipping duplicate data issue

set xact_abort off;
begin tran
DECLARE @error int
    declare @SQL nvarchar(max)
    set @SQL=N'';
select @SQL=some select query to fetch insert scripts
begin try
exec sp_executesql @SQL
 commit
  end try
begin catch
  select @error=@@Error
if @error=2627
  begin
   continue inserting data
  end
if @error<>2627
begin
  rollback
  end
  end catch

I am unable to continue inserting data when any duplicate data comes. Is there any alternative way to continue running SQL queries irrespective of duplicate data? I don not want to alter the index or table.

Upvotes: 0

Views: 139

Answers (1)

TT.
TT.

Reputation: 16146

I am unable to continue inserting data when any duplicate data comes. Is there any alternative way to continue running sql queries irrespective of duplicate data. I dont want to alter the index or table.

What you can do is change the insert scripts as you call them, in this pseudo statement:

select @SQL=some select query to fetch insert scripts
  1. Change the generation script: instead of generating INSERT INTO ... VALUES(...) statements, generate IF NOT EXISTS(...) INSERT INTO ... VALUES(...) statements

These insert statements should first check if a key already exists in the table. If your insert statements are of the form

INSERT INTO some_table(keycol1,...,keycolN,datacol1,...,datacolM)VALUES(keyval1,...,keyvalN,dataval1,...,datavalM);

You can rewrite those as:

IF NOT EXISTS(SELECT 1 FROM some_table WHERE keycol1=keyval1 AND ... AND keycolN=keyvalN)
INSERT INTO some_table(keycol1,...,keycolN,datacol1,...,datacolM)VALUES(keyval1,...,keyvalN,dataval1,...,datavalM);
  1. Change the generation script: instead of generating INSERT INTO ... SELECT ..., generate INSERT INTO ... SELECT ... WHERE NOT EXISTS(...) statements

You can change these statements to only insert if the key does not exist in the table yet. Suppose your insert statements are of the form:

INSERT INTO some_table(keycol1,...,keycolN,datacol1,...,datacolN)
SELECT _keycol1,...,_keycolN,datacol1,...,datacolN 
FROM <from_clause>;

You can rewrite those as:

INSERT INTO some_table(keycol1,...,keycolN,datacol1,...,datacolN)
SELECT _keycol1,...,_keycolN,datacol1,...,datacolN 
FROM <from_clause>
WHERE NOT EXISTS(SELECT 1 FROM some_table WHERE keycol1=_keycol1 AND ... AND keycolN=_keycolN);
  1. Replace the target table name in @SQL with a temporary table (a so-called staging table), then insert from the temporary table to the target table using WHERE NOT EXISTS(...)

This way you would not have to change the insert generation script. First create a temporary table that has the exact same structure as the target table (not including the primary key). Then replace all instances of the target table name in @SQL with the name of the temporary table. Run the @SQL and afterwards insert from the temporary table to the target table using a WHERE NOT EXISTS(...).

Suppose the target table is named some_table, with key columns key_col1,...,key_colN and data columns datacol1, ..., datacolM.

SELECT * INTO #staging_table FROM some_table WHERE 1=0; -- create staging table with same columns as some_table
SET @SQL=REPLACE(@SQL,'some_table','#staging_table');
EXEC sp_executesql @SQL;

INSERT INTO some_table(keycol1,...,keycolN,datacol1,...,datacolN)
SELECT st.keycol1,...,st.keycolN,st.datacol1,...,st.datacolN
FROM #staging_table AS st
WHERE NOT EXISTS(SELECT 1 FROM some_table WHERE keycol1=st.keycol1 AND ... AND keycolN=st.keycolN);

DROP TABLE #staging_table;

Upvotes: 1

Related Questions