sql server - 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 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 unable continue inserting data when duplicate data comes. there alternative way continue running sql queries irrespective of duplicate data? don not want alter index or table.

i unable continue inserting data when duplicate data comes. there alternative way continue running sql queries irrespective of duplicate data. dont want alter index or table.

what can change insert scripts call them, in pseudo statement:

select @sql=some select query fetch insert scripts 

1. change generation script: instead of generating insert ... values(...) statements, generate if not exists(...) insert ... values(...) statements

these insert statements should first check if key exists in table. if insert statements of form

insert some_table(keycol1,...,keycoln,datacol1,...,datacolm)values(keyval1,...,keyvaln,dataval1,...,datavalm); 

you can rewrite as:

if not exists(select 1 some_table keycol1=keyval1 , ... , keycoln=keyvaln) insert some_table(keycol1,...,keycoln,datacol1,...,datacolm)values(keyval1,...,keyvaln,dataval1,...,datavalm); 

2. change generation script: instead of generating insert ... select ..., generate insert ... select ... not exists(...) statements

you can change these statements insert if key not exist in table yet. suppose insert statements of form:

insert some_table(keycol1,...,keycoln,datacol1,...,datacoln) select _keycol1,...,_keycoln,datacol1,...,datacoln  <from_clause>; 

you can rewrite as:

insert some_table(keycol1,...,keycoln,datacol1,...,datacoln) select _keycol1,...,_keycoln,datacol1,...,datacoln  <from_clause> not exists(select 1 some_table keycol1=_keycol1 , ... , keycoln=_keycoln); 

3. replace target table name in @sql temporary table (a so-called staging table), insert temporary table target table using where not exists(...)

this way not have change insert generation script. first create temporary table has exact same structure target table (not including primary key). replace instances of target table name in @sql name of temporary table. run @sql , afterwards insert temporary table target table using where not exists(...).

suppose target table named some_table, key columns key_col1,...,key_coln , data columns datacol1, ..., datacolm.

select * #staging_table some_table 1=0; -- create staging table same columns some_table set @sql=replace(@sql,'some_table','#staging_table'); exec sp_executesql @sql;  insert some_table(keycol1,...,keycoln,datacol1,...,datacoln) select st.keycol1,...,st.keycoln,st.datacol1,...,st.datacoln #staging_table st not exists(select 1 some_table keycol1=st.keycol1 , ... , keycoln=st.keycoln);  drop table #staging_table;