June 7, 2014 at 7:23 pm
I should probably just delete this thread since I got it off on the wrong foot by omitting DDL. Sorry again.
I think I'm going to re-post it next week with DDL and some clarification.
-M
June 7, 2014 at 7:40 pm
Just add the DDL and clarifications to this thread.
No need to start a new one.
June 30, 2014 at 4:10 pm
Okay, not sure if this is of any interest or any use to anyone at this point, but here is basically what I ended up doing:
IF object_id('tempdb..#Existing') IS NOT NULL
DROP TABLE #Existing
IF object_id('tempdb..#Incoming') IS NOT NULL
DROP TABLE #Incoming
IF object_id('tempdb..#Staging') IS NOT NULL
DROP TABLE #Staging
-- Create a table to represent our existing data
CREATE TABLE #Existing
(
Col1 BITNULL,-- "tracked"
Col2 INTNULL, -- "tracked"
Col3 VARCHAR(5)NULL, -- "tracked"
Col4 VARCHAR(5)NULL, -- "tracked"
Col5 VARCHAR(5)NULL, -- NOT "tracked"
Col6 VARCHAR(5)NULL, -- NOT "tracked"
MyDate DATETIME NOT NULL-- NOT "tracked"
)
INSERT INTO #Existing
SELECT 0,100, 'a', 'b','c', NULL, '2014-05-01' UNION ALL-- Row1
SELECT 1,102, 'a', 'b','c', NULL, '2014-05-02' UNION ALL-- Row2
SELECT 1,100, 'a', 'b','c', NULL, '2014-05-03' UNION ALL-- Row3
SELECT 0,101, 'a', 'b','c', NULL, '2014-05-04' UNION ALL-- Row4
SELECT 1,103, 'a', 'b','c', NULL, '2014-05-05' UNION ALL-- Row5
SELECT NULL,102, 'z', 'y','x', 'tom', '2014-05-05' UNION ALL-- Row6
SELECT NULL,101, 'z', 'y','x', 'tom', '2014-05-07'-- Row7
SELECT * FROM #Existing
-- Create a table to represent our new/incoming data
CREATE TABLE #Incoming
(
Col1 BITNULL,-- "tracked"
Col2 INTNULL, -- "tracked"
Col3 VARCHAR(5)NULL, -- "tracked"
Col4 VARCHAR(5)NULL, -- "tracked"
Col5 VARCHAR(5)NULL, -- NOT "tracked"
Col6 VARCHAR(5)NULL, -- NOT "tracked"
MyDate DATETIMENOT NULL-- NOT "tracked"
)
INSERT INTO #Incoming
SELECT 0, 100, 'a', 'b','c', NULL, '2014-06-01' UNION ALL-- Matches Row1
SELECT 1, 101, 'a', 'b','c', NULL, '2014-06-02' UNION ALL -- Unique
SELECT 1, 101, 'a', 'b',NULL, NULL, '2014-06-03' UNION ALL-- Matches Above Row
SELECT 0, 100, 'a', 'b','c', NULL, '2014-06-04' UNION ALL-- Matches Row1
SELECT 1, 100, 'a', 'b',NULL, NULL, '2014-06-05' UNION ALL-- Matches Row3
SELECT NULL, 103, 'z', 'y','x', 'tom', '2014-06-06' UNION ALL-- Unique
SELECT NULL, 102, 'z', 'y','x', 'tom', '2014-06-07'-- Matches Row6
SELECT * FROM #Incoming
-- Create a staging table to hold everything, with existing
-- data flagged so it is easily identifiable.
CREATE TABLE #Staging
(
Col1 BITNULL,-- "tracked"
Col2 INTNULL, -- "tracked"
Col3 VARCHAR(5)NULL, -- "tracked"
Col4 VARCHAR(5)NULL, -- "tracked"
Col5 VARCHAR(5)NULL, -- NOT "tracked"
Col6 VARCHAR(5)NULL, -- NOT "tracked"
MyDate DATETIMENOT NULL, -- NOT "tracked"
IsExisting BITNOT NULL-- FLAG (new field, exclusive to this table)
)
INSERT INTO #Staging
SELECT *, 1 FROM #Existing UNION ALL-- IsExisting = 1
SELECT *, 0 FROM #Incoming-- IsExisting = 0
SELECT * FROM #Staging
;WITH CTE AS
(
SELECT
*,
ROW_NUMBER() OVER
(
PARTITION BY
Col1,
Col2,
Col3,
Col4
ORDER BY
-- Existing data will always be RN = 1
-- Otherwise, we'll keep the newest version.
IsExisting DESC,
MyDate DESC
) AS RN
FROM #Staging
)
-- Delete all but the first (preferably existing) record where
-- the 4 "tracked" fields are identical.
DELETE FROM CTE WHERE RN > 1
SELECT * FROM #Staging
-- Delete all the records we imported from #Existing:
DELETE FROM #Staging WHERE IsExisting = 1
SELECT * FROM #Staging
-- We are now left with a #Staging table that contains only
-- NEW unique values across the 4 "tracked" fields. We can insert
-- these values into our #Existing table, along with the
-- non-tracked fields.
INSERT INTO #Existing
SELECT Col1, Col2, Col3, Col4, Col5, Col6, MyDate FROM #Staging
SELECT * FROM #Existing
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply