我可以输出未插入错误的记录"ignore/Skip duplicate keys sql server "

Question

我在语句中插入了这个内容，以便从select表join中复制值。select获得3000条记录，但当我们运行它时。它忽略/跳过重复的密钥并插入2990行

insert into (col1,col2,col3,col4)
(select col1,col2 col3,col4  from  a FULL  OUTER JOIN  b ON a.ID = b.ID)

我不得不问我如何在不丢失记录的情况下复制数据，我是否可以输出未插入的记录

Answer 1

我认为你必须使用MERGE TSQl并制作像这样的ouytput

BEGIN TRY
--your sql MERGE here 
 OUTPUT $action, inserted.*,deleted.*;
Commit
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
     ROLLBACK
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
       RETURN;
END CATCH
GO