Hi
Leo00,
As Erland said that 'Each statement is atomic, so if one row fails, all other rows are rolled back' , when you insert your rows together and one of them shows error ,
it means the operation is failed.
If you would like to get your requirement , I suggest you to insert your rows one by one. In
my following script , when you insert one row you will execute the procedure 'CatchErrorDemo' once. After you execute the procedure , you will update the error message or insert the row into target table.
Please try it and see if it satisfies your requirement.
---drop table #SourceTable
---drop table #TargetTable
CREATE TABLE #SourceTable (ID INT, Product VARCHAR(100), Qty int , ErrorMessage varchar(2000))
CREATE TABLE #TargetTable (ID INT, Product VARCHAR(6), Qty int not null)
INSERT INTO #SourceTable (ID, Product, Qty)
SELECT 1, 'Bike' , 10 union all
SELECT 10, 'Computer', 8 union all
SELECT 18, 'Phone' , 5 union all
SELECT 23, 'Tablet' , 45 union all
SELECT 100, 'Car', NULL
SELECT * FROM #SourceTable
go
CREATE PROCEDURE CatchErrorDemo
@a int
AS
BEGIN TRY
insert into #TargetTable select ID,Product, Qty from (select *, row_number()over(order by (select 1)) as rn from #SourceTable) t where rn =@a
END TRY
BEGIN CATCH
update a Set a.ErrorMessage=ERROR_MESSAGE()
from (select *, row_number()over(order by (select 1)) as rn from #SourceTable) t
join #SourceTable a on t.ID=a.ID
where t.rn =@a
END CATCH;
EXEC CatchErrorDemo 1;
EXEC CatchErrorDemo 2;
EXEC CatchErrorDemo 3;
EXEC CatchErrorDemo 4;
EXEC CatchErrorDemo 5;
SELECT * FROM #SourceTable
/*
ID Product Qty ErrorMessage
----------- ---------------------------------------------------------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Bike 10 NULL
10 Computer 8 String or binary data would be truncated.
18 Phone 5 NULL
23 Tablet 45 NULL
100 Car NULL Cannot insert the value NULL into column 'Qty', table 'tempdb.dbo.#TargetTable________________________________________________________________________________________________________00000000000D'; column does not allow nulls. INSERT fails.
*/
SELECT * FROM #TargetTable
/*
ID Product Qty
----------- ------- -----------
1 Bike 10
18 Phone 5
23 Tablet 45
*/
Hope it can help you .
Best Regards,
Rachel
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to
MSDN Support, feel free to contact MSDNFSF@microsoft.com.
