June 6, 2014 at 10:40 am
Okay, this one's got me stumped! I want to compare the values of a Scratch table against those of a Live table and eventually insert only the "changed" records from Scratch to Live. Both tables have a couple dozen columns, but we only need to track changes in 8.
Trouble is, all fields are NULLable. And one field is a BIT.
Due to the NULLs, here's what I'd LIKE to do (yes, I know this doesn't work):
;WITH CTE AS
(
SELECT
Col1, -- bit
Col2, -- int
Col3, -- varchar
Col4, -- varchar
Col5, -- varchar
Col6, -- varchar
Col7, -- varchar
Col8 -- varchar
FROM Scratch
INTERSECT
SELECT
Col1, -- bit
Col2, -- int
Col3, -- varchar
Col4, -- varchar
Col5, -- varchar
Col6, -- varchar
Col7, -- varchar
Col8 -- varchar
FROM Live
)
DELETE FROM CTE
-- and then insert the remaining records from Scratch to Live
I gather the first alternative here is to do a WHERE EXISTS query.
DELETE FROM Scratch AS S
WHERE EXISTS
(
SELECT 1
FROM Live AS L
WHERE
L.Col1 = S.Col1 AND
L.Col2 = S.Col2 AND
L.Col3 = S.Col3 AND
L.Col4 = S.Col4 AND
L.Col5 = S.Col5 AND
L.Col6 = S.Col6 AND
L.Col7 = S.Col7 AND
L.Col8 = S.Col8
)
But due to the NULLs, it gets complicated quickly. I could SET ANSI_NULLS OFF; but apparently that feature is deprecated, so I'd rather not introduce it to my code.
I could use ISNULL(col, 'nonsense') on each side of each comparison. I guess that would work for everything but the BIT, which would also have to be CAST to another data type first since there are no possible nonsense values for that field that aren't already in use (NULL, 0, 1). Plus it just seems so inefficient.
Another option might be:
DELETE FROM Scratch AS S
WHERE EXISTS
(
SELECT 1
FROM Live AS L
WHERE
((L.Col1 = S.Col1) OR (L.Col1 IS NULL AND S.Col1 IS NULL)) AND
((L.Col2 = S.Col2) OR (L.Col2 IS NULL AND S.Col2 IS NULL)) AND
((L.Col3 = S.Col3) OR (L.Col3 IS NULL AND S.Col3 IS NULL)) AND
((L.Col4 = S.Col4) OR (L.Col4 IS NULL AND S.Col4 IS NULL)) AND
((L.Col5 = S.Col5) OR (L.Col5 IS NULL AND S.Col5 IS NULL)) AND
((L.Col6 = S.Col6) OR (L.Col6 IS NULL AND S.Col6 IS NULL)) AND
((L.Col7 = S.Col7) OR (L.Col7 IS NULL AND S.Col7 IS NULL)) AND
((L.Col8 = S.Col8) OR (L.Col8 IS NULL AND S.Col8 IS NULL))
)
I dunno, hopefully you get the picture. Any feedback/suggestions/wizary to share?
June 6, 2014 at 10:47 am
What about using MERGE?
_______________________________________________________________
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/
June 6, 2014 at 10:50 am
SQL2K5. 🙁
June 6, 2014 at 10:52 am
It would help if you posted the DDL for the tables, some sample data for the tables and the expected results based on the sample data.
Do you insert data from Scratch that doesn't exist in Live?
Do you delete data from Live that doesn't exist in Scratch?
June 6, 2014 at 10:54 am
Edit: Duplicate post, sorry.
June 6, 2014 at 10:55 am
Sorry Lynn, I wasn't sure DDL was necessary in this case.
Yes, we are importing data from an external source into Scratch, scrubbing it for dupes again Live, and then inserting only the new and modified records to Live.
June 6, 2014 at 11:40 am
autoexcrement (6/6/2014)
Sorry Lynn, I wasn't sure DDL was necessary in this case.Yes, we are importing data from an external source into Scratch, scrubbing it for dupes again Live, and then inserting only the new and modified records to Live.
Unfortunately the following from your original post doesn't really help much:
;WITH CTE AS
(
SELECT
Col1, -- bit
Col2, -- int
Col3, -- varchar
Col4, -- varchar
Col5, -- varchar
Col6, -- varchar
Col7, -- varchar
Col8 -- varchar
FROM Scratch
INTERSECT
SELECT
Col1, -- bit
Col2, -- int
Col3, -- varchar
Col4, -- varchar
Col5, -- varchar
Col6, -- varchar
Col7, -- varchar
Col8 -- varchar
FROM Live
)
DELETE FROM CTE
-- and then insert the remaining records from Scratch to Live
I gather the first alternative here is to do a WHERE EXISTS query.
DELETE FROM Scratch AS S
WHERE EXISTS
(
SELECT 1
FROM Live AS L
WHERE
L.Col1 = S.Col1 AND
L.Col2 = S.Col2 AND
L.Col3 = S.Col3 AND
L.Col4 = S.Col4 AND
L.Col5 = S.Col5 AND
L.Col6 = S.Col6 AND
L.Col7 = S.Col7 AND
L.Col8 = S.Col8
)
What are Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8? I don't mean the data types, but what are they? Are any of these columns a unique or primary key value for the data? This is information that would help in providing you with good answers.
You also mentioned that there are other columns in the data that aren't used in the comparison. What is done with those values? Are they ignored, do they update their respective columns if the columns actually compared are updated in the Live table?
June 6, 2014 at 11:57 am
What are Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8? I don't mean the data types, but what are they? Are any of these columns a unique or primary key value for the data? This is information that would help in providing you with good answers.
Technically, they are not unique or primary keys; I don't think they could be due to all the NULLs. No? But yes, logically, I guess we want that combination of the 8 columns to be unique within this table--but so long as NULL is considered equal to NULL. I'm not sure what else to say, they are just data. Apologies if I'm missing something.
You also mentioned that there are other columns in the data that aren't used in the comparison. What is done with those values? Are they ignored, do they update their respective columns if the columns actually compared are updated in the Live table?
The "other column" values may also change, and if so, yes we would like to insert those new values along with their respective records into Live.
The end goal is that Live should have one record for every unique combination of the 8 "tracked" columns.
Apologies again, sincerely, for the lack of DDL, I really didn't think it was pertinent in this case, but clearly I was wrong there.
June 6, 2014 at 12:22 pm
autoexcrement (6/6/2014)
SQL2K5. 🙁
Derp! I guess you did post in the 2005 forum. 😉
_______________________________________________________________
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/
June 6, 2014 at 1:03 pm
autoexcrement (6/6/2014)
What are Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8? I don't mean the data types, but what are they? Are any of these columns a unique or primary key value for the data? This is information that would help in providing you with good answers.
Technically, they are not unique or primary keys; I don't think they could be due to all the NULLs. No? But yes, logically, I guess we want that combination of the 8 columns to be unique within this table--but so long as NULL is considered equal to NULL. I'm not sure what else to say, they are just data. Apologies if I'm missing something.
You also mentioned that there are other columns in the data that aren't used in the comparison. What is done with those values? Are they ignored, do they update their respective columns if the columns actually compared are updated in the Live table?
The "other column" values may also change, and if so, yes we would like to insert those new values along with their respective records into Live.
The end goal is that Live should have one record for every unique combination of the 8 "tracked" columns.
Apologies again, sincerely, for the lack of DDL, I really didn't think it was pertinent in this case, but clearly I was wrong there.
I can't do anything to help without seeing what it is you are working with, that means DDL, sample data, expected results.
June 6, 2014 at 1:30 pm
I understand. I will try to come up with some good sample DDL. Thanks nonetheless for your efforts.
June 6, 2014 at 1:36 pm
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 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/
June 7, 2014 at 10:11 am
autoexcrement (6/6/2014)
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
June 7, 2014 at 10:15 am
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.
June 7, 2014 at 10:48 am
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply