December 20, 2011 at 3:59 pm
Hello Everyone
I hope that you all are having a very nice day, and are ready for the upcoming holiday.
This is a rather odd request of me, and I am not sure of the best way to be able to do this.
The data that I have coming in, I will insert into a table variable to be able to store until all rows in the hard table have been updated.
There will be very small amounts of data coming in, so I am no so concerened with performance. Approx 4 to 5 rows will be in the Inbound data per day
I need to Insert a new row only if that MemberId is Not already in the table. Everything else is an Update
I need update the data in the table ONLY if there is a NULL value in the column, or If the data coming in is different than that already in
the column. To correct things like a misspelled name, or address, etc....
If there is data in the column, and a NULL value is in the inbound data, do nothing to that particular piece of data.
If there is a NULL value in the column per row, and a NULL value is in the inbound data, do nothing
IF OBJECT_ID('TempDB..#Member','U') IS NOT NULL
DROP TABLE #Member
IF OBJECT_ID('TempDB..#MemberUpdated','U') IS NOT NULL
DROP TABLE #MemberUpdated
CREATE TABLE #Member
(
MemberID int
, DateEntered datetime
, PersonTitle varchar(5)
, FirstName varchar(30)
, LastName varchar(30)
, StreetAddress varchar(50)
, CityName varchar(25)
, Region varchar(25)
, PostalCode varchar(10)
, Country varchar(10)
, EmailAddress varchar(50)
)
CREATE TABLE #MemberUpdated
(
MemberID int
, DateEntered datetime
, PersonTitle varchar(5)
, FirstName varchar(30)
, LastName varchar(30)
, StreetAddress varchar(50)
, CityName varchar(25)
, Region varchar(25)
, PostalCode varchar(10)
, Country varchar(10)
, EmailAddress varchar(50)
)
INSERT INTO
#Member
SELECT 1087572,'May 1 1992 12:00AM','Ms.','Nancy','Davolio','507 - 20th Ave. E. Apt. 2A','Seattle',NULL,'98122',NULL,'Nancy@DoneRightProductions.com' UNION ALL
SELECT 9908634,'Aug 14 1992 12:00AM','Dr.','Andrew','Fuller','908 W. Capital Way','Tacoma','WA','98401','USA','Andrew@DoneRightProductions.com' UNION ALL
SELECT 8364184,'Apr 1 1992 12:00AM',NULL,'Janet','Leverling','722 Moss Bay Blvd.','Kirkland','WA','98033','USA','Janet@DoneRightProductions.com' UNION ALL
SELECT 3829046,'May 3 1993 12:00AM','Mrs.','Margaret','Peacock','4110 Old Redmond Rd.','Redmond','WA','98052','USA',NULL UNION ALL
SELECT 2398450,'Oct 17 1993 12:00AM','Mr.','Stevenn','Buchanan',NULL,'London','UK','SW1 8JR','UK','Steven@DoneRightProductions.com' UNION ALL
SELECT 1894302,'Oct 21 1993 12:00AM','Mr.','Michael','Suyama','Coventry House Miner Rd.','London','UK',NULL,'UK','Michael@DoneRightProductions.com' UNION ALL
SELECT 9109787,'Jan 2 1994 12:00AM','Mr.','Robert','King','Edgeham Hollow Winchester Way','London','UK','RG1 9SP','UK','Robert@DoneRightProductions.com' UNION ALL
SELECT 3388274,'Mar 5 1994 12:00AM','Ms.','Laura','Callahan','4726 - 11th Ave. N.E.','Seattle','WA','98105','USA','Laura@DoneRightProductions.com' UNION ALL
SELECT 2110983,'Nov 15 1994 12:00AM','Ms.','Anne','Dodsworth','7 Houndstooth Rd.','London','UK','WG2 7LT',NULL,'Anne@DoneRightProductions.com'
INSERT INTO #MemberUpdated
SELECT 1087572,'Dec 20 2011 12:00AM','Ms.','Nancy','Davolio' ,'507 - 20th Ave. E. Apt. 2A','Seattle','WA','98122','USA','Nancy@DoneRightProductions.com' UNION ALL
SELECT 9908634,'Aug 14 1992 12:00AM','Dr.','Andrew','Fuller' ,'908 W. Capital Way','Tacoma','WA','98401','USA','Andrew@DoneRightProductions.com' UNION ALL
SELECT 8364184,'Dec 20 2011 12:00AM','Ms.','Janet','Leverling' ,'722 Moss Bay Blvd.','Kirkland','WA','98033','USA','Janet@DoneRightProductions.com' UNION ALL
SELECT 3829046,'Dec 20 2011 12:00AM','Mrs.','Margaret','Peacock' ,'4110 Old Redmond Rd.','Redmond','WA','98052','USA','Margaret@DoneRightProductions.com' UNION ALL
SELECT 2398450,'Dec 20 2011 12:00AM','Mr.','Steven','Buchanan' ,'14 Garrett Hill','London','UK','SW1 8JR','UK','Steven@DoneRightProductions.com' UNION ALL
SELECT 1894302,'Oct 21 1993 12:00AM','Mr.','Michael','Suyama' ,'Coventry House Miner Rd.','London','UK','EC2 7JR','UK','Michael@DoneRightProductions.com' UNION ALL
SELECT 9109787,'Jan 2 1994 12:00AM','Mr.','Robert','King' ,'Edgeham Hollow Winchester Way','London','UK','RG1 9SP','UK','Robert@DoneRightProductions.com' UNION ALL
SELECT 3388274,'Mar 5 1994 12:00AM','Ms.','Laura','Callahan' ,'4726 - 11th Ave. N.E.','Seattle','WA','98105','USA','Laura@DoneRightProductions.com' UNION ALL
SELECT 2110983,'Dec 20 2011 12:00AM','Ms.','Anne','Dodsworth' ,'7 Houndstooth Rd.','London','UK','WG2 7LT','UK','Anne@DoneRightProductions.com'
SELECT * FROM #Member
SELECT * FROM #MemberUpdated
As you notice, there are some NULL values in the Member table. I want to update that NULL value with the text value
from the Update statement, and as long as ALL other values are the same in both the table and the update statement, they can simply update and over
write the existing data, since it will be the same.
So the record for Janet Leverling will now be this:
MemberIDDateEnteredPersonTitleFirstNameLastNameStreetAddressCityNameRegionPostalCodeCountryEmailAddress
83641842011-12-20 00:00:00.000Ms.JanetLeverling722 Moss Bay Blvd.KirklandWA98033USAJanet@DoneRightProductions.com
Or Margaret Peacock will now be this:
MemberIDDateEnteredPersonTitleFirstNameLastNameStreetAddressCityNameRegionPostalCodeCountryEmailAddress
38290462011-12-20 00:00:00.000Mrs.MargaretPeacock4110 Old Redmond Rd.RedmondWA98052USAMargaret@DoneRightProductions.com
The NULL values in both records have been replaced by actual data. And the DateEntered column has been Updated with the CurrentDate, indicating
that something in that row has been modified
I NEVER want to Update the data in a column if there is data in that column, and a NULL value is in the UPDATE statement. I never, ever want to over write
any of the actual data that is in the hard table with a NULL Value.
If there are no changes in the data per row, I can either update it, or skip the update, since the data will not actually change
I do hope that someone has a suggestion on how I can perform this task. Or show me how I can write this code.
If it were not for the old, outdated systems of the world, I would have nothing to do all day. Simply because all the new, modern day systems work.
Thank You in advance so very much for any and all comments, suggestions, code samples, and your valuable time that you have given me, to help me with this problem.
Andrew SQLDBA
December 20, 2011 at 5:37 pm
OK, I don't see NULLS in your sample, and I'm not sure you've clearly stated how you do the updates.
First, you can't manage these updates without some PK or unique identifier. How can you tell that a record from the update table matches the other table? If can't be "every field matches" because you have NULLs.
If it's memberID, then you can match on that in the WHERE clause. So your update is
UPDATE #Member
...
FROM #MemberUpdated mu
WHERE #member.memberid = mu.memberid
In terms of the fields you update, if I read this, it seems as though you want to update the NULL values in #Member with values from #MemberUpdated, correct? If there are NULLs in both tables, I'm not sure you care. For that, you can include something like
SET
#Member.PersonTitle = CASE
WHEN #Member.PersonTitle IS NULL Then mu.PersonTitle Else #Member.PersonTitle
End
That essentially updates the column to itself if it's not NULL, or to the new value if it is.
If you are trying to somehow match everything else, that doesn't make sense. What if you have a NULL in PersonTitle, and then Name, Address, City match, but PostalCode is misspelled in the update? Do nothing? That might be correct, but it often results in less updates than you expect.
December 21, 2011 at 5:18 am
Steve
Thank you for your reply, and sorry that I was not more clear on this issue.
All the data used and shown here is from the Pubs database, but the basic principle of the code is what I need.
The MemberID is the Pri Key, so I can match on that row.
The #MemberUpdated table was only to show that all the records have been updated with actual values. There are NULL values in some of the rows in the #Member, there are also misspellings on some values in some rows.
I have a hard table named 'Member', it is filled with data already. I am using an ETL process to cleanse data as it si coming over from another source. That process stops with the data clean and ready to be either Inserted into the 'Member' table or Update an existing row if that row already exists.
I first take the data and insert it into a table variable, I join the table variable to the 'Member' table on the MemberID to see if any new data needs to be inserted, and if so, Insert that data into the 'Member' table and delete it from the Table Variable
Now, there are still rows in the table variable that need to Update some of the existing data that is already in the 'Member' table. But I have no idea what column for each of the rows need to be updated, so I must update the entire row.
Let me give an example of that:
Existing data in the Member Table:
8364184,'Apr 1 1992 12:00AM',NULL,'Janet','Leverling','722 Moss Bay Blvd.','Kirkland','WA','98033','USA','Janet@DoneRightProductions.com'
Notice the NULL value?
Now, the data sitting in the Table Variable is this:
8364184,'Dec 20 2011 12:00AM','Ms.','Janet','Leverling','722 Moss Bay Blvd.','Kirkland','WA','98033','USA','Janet@DoneRightProductions.com'
The PersonTitle column now has a value, and I need to update that row in the 'Member' Table. The data entered has already been set while the data was in the Table Variable. So I update that row. Pretty simple so far.
The real catch is that I have no idea what column(s) it is that may be updated, it could be all of them, a couple of them, or none of them per row.
I do not want to over write any data in the 'Member table' with a NULL value from the Table Variable. So if there is data in any column in the 'Member' table, I do not want to over write that data with a NULL value that is possibly in the Table Variable.
See this example that is in the 'Member' table:
9908634,'Aug 14 1992 12:00AM','Dr.','Andrew','Fuller' ,'908 W. Capital Way','Tacoma','WA','98401','USA','Andrew@DoneRightProductions.com'
And this data is in the table variable:
9908634,'Dec 20 2011 12:00AM','Dr.','Andrew','Fuller' ,'123 East Main','Tacoma','WA','98401','USA',NULL
I do need to update that row, but I do not want to over write the EmailAddress with the NULL value, only update the StreetAddress, since that has changed. But I realize that the other columns will update also, but they contain the same values in the 'Member' Table and the Table Variable, so that is perfectly fine.
I think that it would take a huge amount of logic to determine what column(s) per row is actually different, so let all the others of this row overwrite. Just as long as a NULL value in the Table Variable data does not over write good data in the 'Member' table.
So to keep things as simple as possible, Update the entire row and each column in that row, as long a NULL, in any column, coming from the Table Variable does not over write good data. or actually any data in the 'Member' table
Could I use a Case statement to include every column in the table to perform the Update? Except of course for the MemberID and the DataEntered columns.
If it were not for the old, outdated systems of the world, I would have nothing to do all day. Simply because all the new, modern day systems work. And this is from a very old and odd system.
Thank You in advance so very much for any and all comments, suggestions, code samples, and your valuable time that you have given me, to help me with this problem.
Andrew SQLDBA
December 21, 2011 at 5:32 am
Hi Andrew
Firstly, try the MERGE statement for upserts, that's what it's for and it makes for clear and performant code.
Secondly, use COALESCE(ExistingColumn, StagingColumn) like this to handle the NULL issue:
ExistingColumn = COALESCE(ExistingColumn, StagingColumn)
This means "use the first non-null value, or null if they are both null" - which gives priority to existing non-null values in your permanent data set.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 21, 2011 at 6:54 am
ChrisM@home (12/21/2011)
Secondly, use COALESCE(ExistingColumn, StagingColumn) like this to handle the NULL issue:ExistingColumn = COALESCE(ExistingColumn, StagingColumn)
Actually it sounds like he wants to overwrite the existing column if both the existing and staging columns contain data. If that is the case then he needs to use
ExistingColumn = COALESCE(StagingColumn, ExistingColumn)
The order simply determines which column takes precedence when both of them contain data. The order is irrelevant when one or both is NULL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 21, 2011 at 7:17 am
Thank you Very much for all your help. My code is now working perfectly. I like the use of Coalesce, much cleaner then using a CASE statement.
I am working on using the MERGE statement now, but having a little trouble with that one.
Thank You again to everyone.
Hope that you have a nice day, and a fabulous holiday
Andrew SQLDBA
December 22, 2011 at 12:43 am
drew.allen (12/21/2011)
ChrisM@home (12/21/2011)
Secondly, use COALESCE(ExistingColumn, StagingColumn) like this to handle the NULL issue:ExistingColumn = COALESCE(ExistingColumn, StagingColumn)
Actually it sounds like he wants to overwrite the existing column if both the existing and staging columns contain data. If that is the case then he needs to use
ExistingColumn = COALESCE(StagingColumn, ExistingColumn)
The order simply determines which column takes precedence when both of them contain data. The order is irrelevant when one or both is NULL.
Drew
Thanks for the catch Drew. That's why you have "intelligence" in your sig and I have "moggies" :hehe:
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply