August 23, 2012 at 11:51 am
Your solution seems like the one! Finally. Hopefully I can put this one to rest. I couldn't seem to get mine to insert the Target rows, anyway. I thought it did at first and then when I tested it again, I didn't see them. So yours wins. I must have inadvertently changed something between my first test and the one I put on here. Oh well, I appreciate the help. This is the completed example:
-- ***************************************************************************
-- * Insert Duplicates Example
-- * This script contains a Source table, a Target table, and an Errors table.
-- * The purpose is to load the Source into the table and divert rows
-- * with duplicate keys to an errors table.
-- * The Target table has a compound primary key.
-- * If a Source table row would violate the primary key on the Target,
-- * the row is diverted to the Errors table.
-- * If there are any rows in the Source that are already duplicated,
-- * they are also diverted. The script loads the values to the Target
-- * twice to insure that there are rows in the Target that will be tested.
-- ***************************************************************************
USE Sandbox
GO
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 ' + CONVERT(VARCHAR(30), GetDate(), 121)
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 ' + CONVERT(VARCHAR(30), GetDate(), 121)
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
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
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply