SQL Server : continue inserting rows after exception

Question

I have a stored procedure that does an insert like this one:

    INSERT INTO [TargetTable] ([Hash], [Year], [Metadata])
        SELECT [Hash], [Year], [Metadata]
        FROM [SourceTable]

This query will fail because the [TargetTable] has data already and an unique index on the [Hash] column. It cannot insert duplicates.

How I can insert only the non-duplicates? I guess I could select first only the non-duplicates and then do the inserts. But the select would be more complex.

Another approach I tried is to ignore the duplicates. For instance:

BEGIN TRY
    INSERT INTO [TargetTable] ([Hash], [Event], [Year], [Metadata])
        SELECT [Hash], [Event], [Year], [Metadata]
        FROM [SourceTable]
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH

I was expecting something like, try to insert this, if you cannot insert it, then ignore it, and then continue inserting the rest of rows.

You seem to be under the belief that SQl Server processes an INSERT, or at least within a BEGIN TRY...CATCH row by row. SQL Server works in datasets and will process the whole dataset in one go. If 1 row out of 1M has an error, then the entire statement errors and is rolled back. When working with a DBMS you need to not think in rows, and think in sets of data. This is why your second attempt didn't work. An INSERT doesn't work like a FOR EACH loop (nor does a SELECT, UPDATE, DELETE`, etc). — Thom A
– Thom A ♦, Commented Nov 17, 2018 at 13:32

Gordon Linoff · Accepted Answer · 2018-11-17 13:23:32Z

2

If no other inserts are happening at the same time, then this should work:

INSERT INTO [TargetTable] ([Hash], [Year], [Metadata])
   SELECT st.[Hash], st.[Year], st.[Metadata]
   FROM (SELECT st.*,
                ROW_NUMBER() OVER (PARTITION BY hash ORDER BY (SELECT NULL)) as seqnum
         FROM SourceTable st
        ) st
   WHERE seqnum = 1 AND -- ensure duplicates are not in source table
         NOT EXISTS (SELECT 1 FROM TargetTable tt WHERE tt.hash = st.hash);

answered Nov 17, 2018 at 13:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : continue inserting rows after exception

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related