How to find out what rows successfuly/failed to insert
in [Programming SQL]
|
Prev: Returning status/error codes as well as resultsets from stored procedure.
Next: Database, SQL Server, .NET
From: Andy B. on 25 Mar 2010 05:56 I have to write code that needs to log down what rows were inserted as well as what ones failed to insert. I use a merge statement to insert/update/delete multiple rows at a time. How would I figure this out?
From: Uri Dimant on 25 Mar 2010 07:21 Andy What do you by as what ones failed to insert.??? Will it generate an error and terminate the whole batch? See the below example from Adam Machanic to use a table variavle along with output clause DECLARE @ChangedOrderDetail SalesOrderDetail_Type DECLARE @SalesOrderID int DECLARE @t TABLE (Changed int, ID int) INSERT INTO @t SELECT Changed, SalesOrderDetailID FROM (MERGE Sales.SalesOrderDetail OrderDetail USING @ChangedOrderDetail Chng ON Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID] AND Chng.[SalesOrderID] = OrderDetail.[SalesOrderID] WHEN MATCHED THEN UPDATE SET [SalesOrderID] = CASE WHEN Chng.Rowversion = OrderDetail.Rowversion THEN Chng.SalesOrderID ELSE OrderDetail.SalesOrderID END ,[CarrierTrackingNumber] = CASE WHEN Chng.Rowversion = OrderDetail.Rowversion THEN Chng.CarrierTrackingNumber ELSE OrderDetail.CarrierTrackingNumber END OUTPUT CASE WHEN Chng.Rowversion = deleted.Rowversion THEN 1 ELSE 0 END Changed , Chng.SalesOrderDetailID) da WHERE CHanged = 0 SELECT * FROM @t "Andy B." <a_borka(a)sbcglobal.net> wrote in message news:uc3$vFAzKHA.1236(a)TK2MSFTNGP06.phx.gbl... >I have to write code that needs to log down what rows were inserted as well >as what ones failed to insert. I use a merge statement to >insert/update/delete multiple rows at a time. How would I figure this out? >
From: Andy B. on 25 Mar 2010 08:19 What I mean is that if rows 1,3 and 5 failed to insert because of a unique key violation (or some other reason), I need to be able to identify those rows so they can be written to a log somewhere. "Uri Dimant" <urid(a)iscar.co.il> wrote in message news:uarf50AzKHA.1064(a)TK2MSFTNGP04.phx.gbl... > Andy > What do you by as what ones failed to insert.??? Will it generate an > error and terminate the whole batch? > See the below example from Adam Machanic to use a table variavle along > with output clause > > DECLARE @ChangedOrderDetail SalesOrderDetail_Type > DECLARE @SalesOrderID int > > > DECLARE @t TABLE (Changed int, ID int) > > > > INSERT INTO @t > SELECT Changed, SalesOrderDetailID > FROM (MERGE Sales.SalesOrderDetail OrderDetail > USING @ChangedOrderDetail Chng ON Chng.[SalesOrderDetailID] > = OrderDetail.[SalesOrderDetailID] > > AND Chng.[SalesOrderID] = > OrderDetail.[SalesOrderID] > > > WHEN MATCHED THEN > UPDATE SET [SalesOrderID] = CASE WHEN Chng.Rowversion = > OrderDetail.Rowversion > THEN Chng.SalesOrderID > > ELSE > OrderDetail.SalesOrderID > > END > > ,[CarrierTrackingNumber] = CASE WHEN Chng.Rowversion = > OrderDetail.Rowversion > > THEN > Chng.CarrierTrackingNumber > > ELSE > OrderDetail.CarrierTrackingNumber > > END > > OUTPUT CASE WHEN Chng.Rowversion = deleted.Rowversion THEN 1 ELSE 0 > END Changed > > , Chng.SalesOrderDetailID) da > > WHERE CHanged = 0 > > > > SELECT * > > FROM @t > > > > > > "Andy B." <a_borka(a)sbcglobal.net> wrote in message > news:uc3$vFAzKHA.1236(a)TK2MSFTNGP06.phx.gbl... >>I have to write code that needs to log down what rows were inserted as >>well as what ones failed to insert. I use a merge statement to >>insert/update/delete multiple rows at a time. How would I figure this out? >> > >
From: Uri Dimant on 25 Mar 2010 08:27 Andy B Hmm you can easily indentify by running WHERE EXISTS clause INSERT INTO tracktable SELECT * FROM destination WHERE EXISTS (SELECT * FROM source WHERE source.uniquecol=destination.uniquecol) Read Erland's article http://www.sommarskog.se/error-handling-I.html "Andy B." <a_borka(a)sbcglobal.net> wrote in message news:eESymVBzKHA.264(a)TK2MSFTNGP05.phx.gbl... > What I mean is that if rows 1,3 and 5 failed to insert because of a unique > key violation (or some other reason), I need to be able to identify those > rows so they can be written to a log somewhere. > "Uri Dimant" <urid(a)iscar.co.il> wrote in message > news:uarf50AzKHA.1064(a)TK2MSFTNGP04.phx.gbl... >> Andy >> What do you by as what ones failed to insert.??? Will it generate an >> error and terminate the whole batch? >> See the below example from Adam Machanic to use a table variavle along >> with output clause >> >> DECLARE @ChangedOrderDetail SalesOrderDetail_Type >> DECLARE @SalesOrderID int >> >> >> DECLARE @t TABLE (Changed int, ID int) >> >> >> >> INSERT INTO @t >> SELECT Changed, SalesOrderDetailID >> FROM (MERGE Sales.SalesOrderDetail OrderDetail >> USING @ChangedOrderDetail Chng ON >> Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID] >> >> AND Chng.[SalesOrderID] = >> OrderDetail.[SalesOrderID] >> >> >> WHEN MATCHED THEN >> UPDATE SET [SalesOrderID] = CASE WHEN Chng.Rowversion = >> OrderDetail.Rowversion >> THEN Chng.SalesOrderID >> >> ELSE >> OrderDetail.SalesOrderID >> >> END >> >> ,[CarrierTrackingNumber] = CASE WHEN Chng.Rowversion = >> OrderDetail.Rowversion >> >> THEN >> Chng.CarrierTrackingNumber >> >> ELSE >> OrderDetail.CarrierTrackingNumber >> >> END >> >> OUTPUT CASE WHEN Chng.Rowversion = deleted.Rowversion THEN 1 ELSE 0 >> END Changed >> >> , Chng.SalesOrderDetailID) da >> >> WHERE CHanged = 0 >> >> >> >> SELECT * >> >> FROM @t >> >> >> >> >> >> "Andy B." <a_borka(a)sbcglobal.net> wrote in message >> news:uc3$vFAzKHA.1236(a)TK2MSFTNGP06.phx.gbl... >>>I have to write code that needs to log down what rows were inserted as >>>well as what ones failed to insert. I use a merge statement to >>>insert/update/delete multiple rows at a time. How would I figure this >>>out? >>> >> >> > >
From: Plamen Ratchev on 25 Mar 2010 10:18 You can check for dups up front (or in the MERGE statement) using the ranking functions and insert only rows with rank 1, all other rows you can log to a log table. -- Plamen Ratchev http://www.SQLStudio.com
|
Next
|
Last
Pages: 1 2 Prev: Returning status/error codes as well as resultsets from stored procedure. Next: Database, SQL Server, .NET |