Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

DELETE + EXCEPT/INTERSECT + NULLS + BITS! Expand / Collapse
Author
Message
Posted Friday, June 6, 2014 1:30 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:23 AM
Points: 156, Visits: 599
I understand. I will try to come up with some good sample DDL. Thanks nonetheless for your efforts.


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1578473
Posted Friday, June 6, 2014 1:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,085, Visits: 12,551
autoexcrement (6/6/2014)
I understand. I will try to come up with some good sample DDL. Thanks nonetheless for your efforts.


Here is a start.

create table scratch
(
Col1 bit
,Col2 int
,Col3 varchar
,Col4 varchar
,Col5 varchar
,Col6 varchar
,Col7 varchar
,Col8 varchar
)

create table live
(
Col1 bit
,Col2 int
,Col3 varchar
,Col4 varchar
,Col5 varchar
,Col6 varchar
,Col7 varchar
,Col8 varchar
,Col9 varchar
,Col10 varchar
)


Now we just some sample data and what you are expecting as a result.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1578477
Posted Saturday, June 7, 2014 10:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:06 AM
Points: 1,917, Visits: 19,596
autoexcrement (6/6/2014)
[quote]
The end goal is that Live should have one record for every unique combination of the 8 "tracked" columns.

.


maybe an idea?

INSERT INTO [dbo].[live]
([col1]
,[col2]
,[col3]
,[col4]
,[col5]
,[col6]
,[col7]
,[col8])
(
SELECT [col1]
,[col2]
,[col3]
,[col4]
,[col5]
,[col6]
,[col7]
,[col8]
FROM scratch
EXCEPT
SELECT [col1]
,[col2]
,[col3]
,[col4]
,[col5]
,[col6]
,[col7]
,[col8]
FROM live
)
GO

with reference to NULLs

http://msdn.microsoft.com/en-gb/library/ms188055(v=sql.90).aspx

When you compare rows for determining distinct values, two NULL values are considered equal.


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1578580
Posted Saturday, June 7, 2014 10:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:23 AM
Points: 156, Visits: 599
Thanks for the reply! The problem is we still need the "other columns". Again, it's my bad for not getting the DDL together for this one first. I will try to do that next week.


"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1578582
Posted Saturday, June 7, 2014 10:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:06 AM
Points: 1,917, Visits: 19,596
autoexcrement (6/7/2014)
Thanks for the reply! The problem is we still need the "other columns". Again, it's my bad for not getting the DDL together for this one first. I will try to do that next week.


hmmm....what do you want to do when your " 8 col unique key" from scratch matches a row in live,,,,but the "other columns" are not the same?


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1578583
Posted Saturday, June 7, 2014 7:23 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:23 AM
Points: 156, Visits: 599
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



"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1578616
Posted Saturday, June 7, 2014 7:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:02 PM
Points: 20,734, Visits: 32,515
Just add the DDL and clarifications to this thread.

No need to start a new one.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1578617
Posted Monday, June 30, 2014 4:10 PM This worked for the OP Answer marked as solution


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:23 AM
Points: 156, Visits: 599
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 BIT NULL, -- "tracked"
Col2 INT NULL, -- "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 BIT NULL, -- "tracked"
Col2 INT NULL, -- "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 #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 BIT NULL, -- "tracked"
Col2 INT NULL, -- "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"
IsExisting BIT NOT 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




"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1587818
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse