INSERT into Target table from Source table, redirecting duplicate key rows into ERROR table
-
1) Source tables are heaps
2) Target tables are identical except with primary key columns. They often have data in them already.
3) Error table is the same as the source table with an [Error_Description] column.
My goal is to load to load all the source data into the target table WITHOUT deleting the source table data.
The source data could have duplicate key columns in it already and/or a duplicate key row could already exist in the target table.
Here is a script that at this point runs but fails to put anything into either table, obviously because of the key violation. If someone could help me add the code that would put the duplicates into the error table and the the good rows into the target, I would appreciate it. Thank you.
USE SandboxGO
SET NOCOUNT ON
GO
CREATE TABLE Source
(
[DayKey] INT,
[holi_id] INT,
[holi_desc] VARCHAR(1000),
[holi_dt] DATETIME
)
GO
CREATE TABLE Target
(
[DayKey] INT NOT NULL,
[holi_id] INT NOT NULL,
[holi_desc] VARCHAR(1000),
[holi_dt] DATETIME
)
GO
ALTER TABLE Target ADD CONSTRAINT pk_Target PRIMARY KEY (DayKey, holi_id)
GO
CREATE TABLE Errors
(
[DayKey] INT,
[holi_id] INT,
[holi_desc] VARCHAR(1000),
[holi_dt] DATETIME,
[Error_Description] VARCHAR(4000)
)
GO
CREATE PROCEDURE [dbo].[LOAD_Source]
AS
DECLARE @intDayKey int
SET @intDayKey = 20120817
TRUNCATE TABLE dbo.Source
INSERT INTO dbo.Source
([DayKey]
,[holi_id]
,[holi_desc]
,[holi_dt])
VALUES(@intDayKey, 1, 'NEW YEARS','2012-01-01'),
(@intDayKey, 2, 'MARTIN LUTHER KING DAY','2012-01-16'),
(@intDayKey, 3, 'GROUND HOG DAY','2012-02-02'),
(@intDayKey, 2, 'PRESIDENTS DAY','2012-02-20'),
(@intDayKey, 3, 'SAINT PATRICKS DAY','2012-03-17'),
(@intDayKey, 3, 'GOOD FRIDAY','2012-04-06'),
(@intDayKey, 4, 'EASTER SUNDAY','2012-04-08'),
(@intDayKey, 5, 'MEMORIAL DAY','2012-05-28'),
(@intDayKey, 6, 'INDEPENDENCE DAY','2012-07-04'),
(@intDayKey, 7, 'LABOR DAY','2012-09-03'),
(@intDayKey, 8, 'COLUMBUS DAY','2012-10-08'),
(@intDayKey, 8, 'ELECTION DAY','2012-11-06'),
(@intDayKey, 9, 'VETERANS DAY','2012-11-11'),
(@intDayKey, 10, 'THANKSGIVING DAY','2012-11-22'),
(@intDayKey, 11, 'BLACK FRIDAY','2012-11-23'),
(@intDayKey, 12, 'CHRISTMAS EVE','2012-12-24'),
(@intDayKey, 13, 'CHRISTMAS DAY','2012-12-25'),
(@intDayKey, 14, 'NEW YEARS EVE','2012-12-31')
GO
EXEC LOAD_Source
GO
CREATE PROCEDURE [dbo].[LOAD_Target]
AS
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO dbo.Target
([DayKey]
,[holi_id]
,[holi_desc]
,[holi_dt])
SELECT[DayKey]
,[holi_id]
,[holi_desc]
,[holi_dt]
FROM dbo.Source
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT CAST(GETDATE() AS VARCHAR(50))+' Error: '+COALESCE(STR(ERROR_NUMBER()),'')+' '+COALESCE(ERROR_MESSAGE(),'')+' '+COALESCE(STR(ERROR_LINE()),'')+COALESCE(ERROR_PROCEDURE(),'')
RETURN 1
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
GO
EXEC LOAD_Target
GO
SELECT * FROM Source
SELECT * ,'Target' AS Source FROM Target
SELECT * ,'Errors' AS Source FROM Errors
DROP PROCEDURE LOAD_Target
DROP PROCEDURE LOAD_Source
DROP TABLE Errors
DROP TABLE Target
DROP TABLE Source
GO
SET NOCOUNT OFF
GO
-
Use SSIS, with Ado.net sources and destinations. You will then be able to "redirect rows" on failure.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Holy cow!!! You have 9 points and your post was absolutely perfect!!! It included all the ddl, sample data, desired output, a clear explanation of what you wanted. I wish more people would post like yours. BRAVO!!!!
I am not sure what other exceptions you could encounter here assuming the tables exist and permissions are ok. That means the easy way to do this would be to first insert the duplicate rows to the error table and then insert the remainder in the target table.
BEGIN TRY
--First insert the rows we know will fail
INSERT Errors
select DayKey, holi_id, holi_dt, 'Duplicate Key'
from Source
where DayKey in
(
select DayKey from Target
)
--now only insert those rows that are not duplicates
INSERT INTO dbo.Target
([DayKey]
,[holi_id]
,[holi_desc]
,[holi_dt])
SELECT[DayKey]
,[holi_id]
,[holi_desc]
,[holi_dt]
FROM dbo.Source
where DayKey not in
(
select DayKey from Target
)
COMMIT TRANSACTION
END TRY
I would keep you try catch to handle any other type of error. Notice I also moved the COMMIT to the end of the TRY block. There is no need to have it in a separate if statement.
Let me know if that works for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thank you very much - we are very close now. But what if there are duplicates in the source - not counting the target? How do we get those out? Or did I miss something here?
-
dcwilson2009 (8/22/2012)
Thank you very much - we are very close now. But what if there are duplicates in the source - not counting the target? How do we get those out? Or did I miss something here?Ahh I must have missed that part. How do you want to handle that? If for example there were two rows in the source with the same key what happens to those rows? Do we insert 1 row into each table (1 that makes it and another to indicate it would have generated a duplicate)?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thank you for your quick reply. I would just want to put them both into the error table, maybe even with a different error description. It's impossible to evaluate them without looking manually at this point.
-
dcwilson2009 (8/22/2012)
Thank you for your quick reply. I would just want to put them both into the error table, maybe even with a different error description. It's impossible to evaluate them without looking manually at this point.Then you just need another insert into your errors table.
I think should do it.
INSERT Errors
select DayKey, holi_id, holi_dt, 'Duplicate Keys in Source Table'
from Source
where DayKey in
(
select DayKey
from Source
group by DayKey
having COUNT(DayKey) > 1
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
This is really good so far. Thank you very much. I forgot to mention in the last post that holi_id is part of the primary key so I have to figure out how to adapt this to excluding duplicates based on a compound key. I have this so far:
--First insert the rows we know will failINSERT Errors
select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Key in Target Table ' + CAST(GetDate() AS VARCHAR(50))
from Source
where DayKey in
(
select DayKey from Target
)
INSERT Errors
select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Keys in Source Table ' + CAST(GetDate() AS VARCHAR(50))
from Source
where DayKey in
(
select DayKey
from Source
group by DayKey
having COUNT(DayKey) > 1
)
--now only insert those rows that are not duplicates
INSERT INTO dbo.Target
([DayKey]
,[holi_id]
,[holi_desc]
,[holi_dt])
SELECT[DayKey]
,[holi_id]
,[holi_desc]
,[holi_dt]
FROM dbo.Source
where DayKey not in
(
select DayKey from Target
)
-
I tried this but it returned no results from either the target or the errors table and there were no errors returned:
--First insert the rows we know will failINSERT Errors
select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Key in Target Table ' + CAST(GetDate() AS VARCHAR(50))
from Source
where exists
(
select DayKey, holi_id from Target
)
INSERT Errors
select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Keys in Source Table ' + CAST(GetDate() AS VARCHAR(50))
from Source
where exists
(
select DayKey, holi_id
from Source
group by DayKey, holi_id
having COUNT(*) > 1
)
--now only insert those rows that are not duplicates
INSERT INTO dbo.Target
([DayKey]
,[holi_id]
,[holi_desc]
,[holi_dt])
SELECT[DayKey]
,[holi_id]
,[holi_desc]
,[holi_dt]
FROM dbo.Source
where not exists
(
select DayKey, holi_id from Target
)
-
That is because you changed the not in to exists. Exists does not work like that. It evaluates to true or false and does the action accordingly. You should change them back to using IN.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I have been struggling for hours to get this and I cannot figure out how to use the IN clause when there are 2 columns making up the primary key.
-
I am struggling greatly to get the IN clause to work with a compound 2 column key. If you could help me get that, I think I may have it. I tried this and it selected the duplicate rows in Source to put in Errors, but when I tried to negate it to get the rest of the rows into Target, I got nothing:
select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Keys in Source Table ' + CAST(GetDate() AS VARCHAR(50))from Source
where DayKey in
(
select DayKey
from Source
group by DayKey
having COUNT(*) > 1
)
and holi_id in
(
select holi_id
from Source
group by holi_id
having COUNT(*) > 1
)
I have many tables to do this to (with compound keys), so once I get this, you will have helped me over a huge hump. Thank you so far.
-
Try this
with cte as (select DayKey, holi_id, holi_desc, holi_dt,
count(*) over(partition by DayKey, holi_id) as cn
from Source)
select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Keys in Source Table ' + CAST(GetDate() AS VARCHAR(50))
from cte
where cn>1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank you for your response. While you were coming up with that, I came up with this as an entire solution finally. If you can, please take a look at it and see if you see any pitfalls or any improvements I can make. I will also try your script. The one below is complete and can be run and checked immediately without any further manipulation (except the database name)
USE SandboxGO
SET NOCOUNT ON
GO
CREATE TABLE Source
(
[DayKey] INT,
[holi_id] INT,
[holi_desc] VARCHAR(1000),
[holi_dt] DATETIME
)
GO
CREATE TABLE Target
(
[DayKey] INT NOT NULL,
[holi_id] INT NOT NULL,
[holi_desc] VARCHAR(1000),
[holi_dt] DATETIME
)
GO
ALTER TABLE Target ADD CONSTRAINT pk_Target PRIMARY KEY (DayKey, holi_id)
GO
CREATE TABLE Errors
(
[DayKey] INT,
[holi_id] INT,
[holi_desc] VARCHAR(1000),
[holi_dt] DATETIME,
[Error_Description] VARCHAR(4000)
)
GO
CREATE PROCEDURE [dbo].[LOAD_Source]
AS
DECLARE @intDayKey int
SET @intDayKey = 20120817
INSERT INTO dbo.Source
([DayKey]
,[holi_id]
,[holi_desc]
,[holi_dt])
VALUES(@intDayKey, 1, 'NEW YEARS','2012-01-01'),
(@intDayKey, 2, 'MARTIN LUTHER KING DAY','2012-01-16'),
(@intDayKey, 3, 'GROUND HOG DAY','2012-02-02'),
(@intDayKey, 2, 'PRESIDENTS DAY','2012-02-20'),
(@intDayKey, 3, 'SAINT PATRICKS DAY','2012-03-17'),
(@intDayKey, 3, 'GOOD FRIDAY','2012-04-06'),
(@intDayKey, 4, 'EASTER SUNDAY','2012-04-08'),
(@intDayKey, 5, 'MEMORIAL DAY','2012-05-28'),
(@intDayKey, 6, 'INDEPENDENCE DAY','2012-07-04'),
(@intDayKey, 7, 'LABOR DAY','2012-09-03'),
(@intDayKey, 8, 'COLUMBUS DAY','2012-10-08'),
(@intDayKey, 8, 'ELECTION DAY','2012-11-06'),
(@intDayKey, 9, 'VETERANS DAY','2012-11-11'),
(@intDayKey, 10, 'THANKSGIVING DAY','2012-11-22'),
(@intDayKey, 11, 'BLACK FRIDAY','2012-11-23'),
(@intDayKey, 12, 'CHRISTMAS EVE','2012-12-24'),
(@intDayKey, 13, 'CHRISTMAS DAY','2012-12-25'),
(@intDayKey, 14, 'NEW YEARS EVE','2012-12-31')
GO
EXEC LOAD_Source
GO
CREATE PROCEDURE [dbo].[LOAD_Target]
AS
BEGIN TRANSACTION
BEGIN TRY
--First insert the rows we know will fail
INSERT Errors
select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Key in Target Table ' + CAST(GetDate() AS VARCHAR(50))
from Source
where exists
(
select t.DayKey, t.holi_id from Target t
inner join Source s on s.DayKey = t.DayKey and s.holi_id = t.holi_id
)
INSERT Errors
select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Keys in Source Table ' + CAST(GetDate() AS VARCHAR(50))
from Source
where DayKey in
(
select DayKey
from Source
group by DayKey
having COUNT(*) > 1
)
and holi_id in
(
select holi_id
from Source
group by holi_id
having COUNT(*) > 1
)
--now only insert those rows that are not duplicates
INSERT Target
select DayKey, holi_id, holi_desc, holi_dt
from Source
where not
(
DayKey in
(
select DayKey
from Source
group by DayKey
having COUNT(*) > 1
)
and holi_id in
(
select holi_id
from Source
group by holi_id
having COUNT(*) > 1
)
)
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT CAST(GETDATE() AS VARCHAR(50))+' Error: '+COALESCE(STR(ERROR_NUMBER()),'')+' '+COALESCE(ERROR_MESSAGE(),'')+' '+COALESCE(STR(ERROR_LINE()),'')+COALESCE(ERROR_PROCEDURE(),'')
RETURN 1
END CATCH
GO
-- Load Target initially
EXEC LOAD_Target
GO
-- Load Target again, causing an attempt to add rows already existing in Target
EXEC LOAD_Target
GO
-- Select from all 3 tables, adding a TableName column to identify the results
SELECT * ,'Source' AS TableName FROM Source
SELECT * ,'Target' AS TableName FROM Target
SELECT * ,'Errors' AS TableName FROM Errors
DROP PROCEDURE LOAD_Target
DROP PROCEDURE LOAD_Source
DROP TABLE Errors
DROP TABLE Target
DROP TABLE Source
GO
SET NOCOUNT OFF
GO
-
dcwilson2009 (8/23/2012)
Thank you for your response. While you were coming up with that, I came up with this as an entire solution finally. If you can, please take a look at it and see if you see any pitfalls or any improvements I can make. I will also try your script. The one below is complete and can be run and checked immediately without any further manipulation (except the database name)USE SandboxGO
SET NOCOUNT ON
GO
CREATE TABLE Source
(
[DayKey] INT,
[holi_id] INT,
[holi_desc] VARCHAR(1000),
[holi_dt] DATETIME
)
GO
CREATE TABLE Target
(
[DayKey] INT NOT NULL,
[holi_id] INT NOT NULL,
[holi_desc] VARCHAR(1000),
[holi_dt] DATETIME
)
GO
ALTER TABLE Target ADD CONSTRAINT pk_Target PRIMARY KEY (DayKey, holi_id)
GO
CREATE TABLE Errors
(
[DayKey] INT,
[holi_id] INT,
[holi_desc] VARCHAR(1000),
[holi_dt] DATETIME,
[Error_Description] VARCHAR(4000)
)
GO
CREATE PROCEDURE [dbo].[LOAD_Source]
AS
DECLARE @intDayKey int
SET @intDayKey = 20120817
INSERT INTO dbo.Source
([DayKey]
,[holi_id]
,[holi_desc]
,[holi_dt])
VALUES(@intDayKey, 1, 'NEW YEARS','2012-01-01'),
(@intDayKey, 2, 'MARTIN LUTHER KING DAY','2012-01-16'),
(@intDayKey, 3, 'GROUND HOG DAY','2012-02-02'),
(@intDayKey, 2, 'PRESIDENTS DAY','2012-02-20'),
(@intDayKey, 3, 'SAINT PATRICKS DAY','2012-03-17'),
(@intDayKey, 3, 'GOOD FRIDAY','2012-04-06'),
(@intDayKey, 4, 'EASTER SUNDAY','2012-04-08'),
(@intDayKey, 5, 'MEMORIAL DAY','2012-05-28'),
(@intDayKey, 6, 'INDEPENDENCE DAY','2012-07-04'),
(@intDayKey, 7, 'LABOR DAY','2012-09-03'),
(@intDayKey, 8, 'COLUMBUS DAY','2012-10-08'),
(@intDayKey, 8, 'ELECTION DAY','2012-11-06'),
(@intDayKey, 9, 'VETERANS DAY','2012-11-11'),
(@intDayKey, 10, 'THANKSGIVING DAY','2012-11-22'),
(@intDayKey, 11, 'BLACK FRIDAY','2012-11-23'),
(@intDayKey, 12, 'CHRISTMAS EVE','2012-12-24'),
(@intDayKey, 13, 'CHRISTMAS DAY','2012-12-25'),
(@intDayKey, 14, 'NEW YEARS EVE','2012-12-31')
GO
EXEC LOAD_Source
GO
CREATE PROCEDURE [dbo].[LOAD_Target]
AS
BEGIN TRANSACTION
BEGIN TRY
--First insert the rows we know will fail
INSERT Errors
select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Key in Target Table ' + CAST(GetDate() AS VARCHAR(50))
from Source
where exists
(
select t.DayKey, t.holi_id from Target t
inner join Source s on s.DayKey = t.DayKey and s.holi_id = t.holi_id
)
INSERT Errors
select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Keys in Source Table ' + CAST(GetDate() AS VARCHAR(50))
from Source
where DayKey in
(
select DayKey
from Source
group by DayKey
having COUNT(*) > 1
)
and holi_id in
(
select holi_id
from Source
group by holi_id
having COUNT(*) > 1
)
--now only insert those rows that are not duplicates
INSERT Target
select DayKey, holi_id, holi_desc, holi_dt
from Source
where not
(
DayKey in
(
select DayKey
from Source
group by DayKey
having COUNT(*) > 1
)
and holi_id in
(
select holi_id
from Source
group by holi_id
having COUNT(*) > 1
)
)
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT CAST(GETDATE() AS VARCHAR(50))+' Error: '+COALESCE(STR(ERROR_NUMBER()),'')+' '+COALESCE(ERROR_MESSAGE(),'')+' '+COALESCE(STR(ERROR_LINE()),'')+COALESCE(ERROR_PROCEDURE(),'')
RETURN 1
END CATCH
GO
-- Load Target initially
EXEC LOAD_Target
GO
-- Load Target again, causing an attempt to add rows already existing in Target
EXEC LOAD_Target
GO
-- Select from all 3 tables, adding a TableName column to identify the results
SELECT * ,'Source' AS TableName FROM Source
SELECT * ,'Target' AS TableName FROM Target
SELECT * ,'Errors' AS TableName FROM Errors
DROP PROCEDURE LOAD_Target
DROP PROCEDURE LOAD_Source
DROP TABLE Errors
DROP TABLE Target
DROP TABLE Source
GO
SET NOCOUNT OFF
GO
I'd do it like this
CREATE PROCEDURE [dbo].[LOAD_Target]AS
BEGIN TRANSACTION
BEGIN TRY
--First insert the rows we know will fail
INSERT Errors
select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Key in Target Table ' + CAST(GetDate() AS VARCHAR(50))
from Source s
where exists
(
select * from Target t
where s.DayKey = t.DayKey and s.holi_id = t.holi_id
);
with cte as (
select DayKey, holi_id, holi_desc, holi_dt,
count(*) over(partition by DayKey, holi_id) as cn
from Source)
INSERT Errors
select DayKey, holi_id, holi_desc, holi_dt, 'Duplicate Keys in Source Table ' + CAST(GetDate() AS VARCHAR(50))
from cte
where cn > 1;
--now only insert those rows that are not duplicates
with cte as (
select DayKey, holi_id, holi_desc, holi_dt,
count(*) over(partition by DayKey, holi_id) as cn
from Source)
INSERT Target
select DayKey, holi_id, holi_desc, holi_dt
from cte s
where cn = 1
and not exists
(
select * from Target t
where s.DayKey = t.DayKey and s.holi_id = t.holi_id
);
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT CAST(GETDATE() AS VARCHAR(50))+' Error: '+COALESCE(STR(ERROR_NUMBER()),'')+' '+COALESCE(ERROR_MESSAGE(),'')+' '+COALESCE(STR(ERROR_LINE()),'')+COALESCE(ERROR_PROCEDURE(),'')
RETURN 1
END CATCH
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply