SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MERGE problem -> when I need to reenter an entry which is already in the history


MERGE problem -> when I need to reenter an entry which is already in the history

Author
Message
Golden_eye
Golden_eye
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 228
Hi,
I am having a BIG problem with the merge statement in SQL 2008:

When I have an entry (for example an employee name) in the table which is already old and saved in the history ->if it is added again it appears in the results as old..... For example if John Smith is part of the company then leaves (it is saved in the history as an obsolete entry) and then again comes back to the company in the result sections John is still obsolete...???!!!

Help please!!!
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25805 Visits: 17509
Hi and welcome to SSC. You seem to have a challenge you are dealing with. Unfortunately you haven't posted anywhere near enough information for anybody to be able to provide much help. Please take a few minutes to read the article in my signature about best practices when posting questions.

_______________________________________________________________

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.

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)
Golden_eye
Golden_eye
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 228
Here the script

CREATE TABLE Persons
(
ID int IDENTITY PRIMARY KEY
,FirstName varchar(20)
,LastName varchar (20)
,Title varchar(10)
,IsRowCurrent tinyint
,LastUpdated DATE
,ValidFrom DATE
,ValidTo DATE
)
--Populate Persons table with existing customers

INSERT INTO Persons
VALUES ('Anna','Kournikova','Miss',1,'2012-11-01','2012-11-01','9999-12-31')
,('Roger','Federer','Mr',1,'2012-11-01','2012-11-01','9999-12-31')
GO
--Create an update table to hold new/updated persons

CREATE TABLE NewPersons
(
FirstName varchar (20)
,LastName varchar (20)
,Title varchar(10)
)
--Populate People table with existing people
INSERT INTO NewPersons
VALUES ('Anna','Smith','Mrs') --Update
,('Rafael','Nadal','Mr') --New entry
GO
---------------------------------------------------------------------------

-- Mixed Merge - Type 1 and 2

INSERT INTO Persons
( FirstName
,LastName
,Title
,IsRowCurrent
,LastUpdated
,ValidFrom
,ValidTo
) SELECT
FirstName
,LastName
,Title
,1 --IsRowCurrent
,GETDATE() --LastUpdated
,GETDATE() --ValidFrom
,'9999-12-31' --ValidTo
FROM (
MERGE INTO Persons AS [Target]
USING NewPersons AS [Source]
ON Target.FirstName = Source.FirstName


WHEN MATCHED and Target.IsRowCurrent = 1 -- SCD 1/2 changes
AND
(
Target.FirstName <> Source.FirstName
OR Target.LastName <> Source.LastName
OR Target.Title <> Source.Title
)
THEN UPDATE SET

Target.IsRowCurrent = 0 --Obsolete
,Target.LastUpdated = GETDATE()
,Target.ValidTo = GETDATE()

WHEN NOT MATCHED -- New entries
THEN INSERT (
FirstName
,LastName
,Title
,IsRowCurrent
,LastUpdated
,ValidFrom
,ValidTo
)
Values (
Source.FirstName
,Source.LastName
,Source.Title
,1 --IsRowCurrent
,GETDATE() --LastUpdated
,GETDATE() --ValidFrom
,'9999-12-31' --ValidTo
)
WHEN NOT MATCHED BY SOURCE -- Obsolete removed entries
AND Target.IsRowCurrent = 1
THEN UPDATE SET
Target.IsRowCurrent = 0 --Removed
,Target.LastUpdated = GETDATE()
,Target.ValidTo = GETDATE()

OUTPUT $action AS Action
,[Source].*
) AS MergeOutput
WHERE MergeOutput.Action = 'UPDATE'
AND FirstName IS NOT NULL;

If Anna is removed and afterwards she is entyered again, she still appears as unactive (IsRowCurrent=0)...
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 19524
chingarova (3/4/2013)
Hi,
I am having a BIG problem with the merge statement in SQL 2008:

When I have an entry (for example an employee name) in the table which is already old and saved in the history ->if it is added again it appears in the results as old..... For example if John Smith is part of the company then leaves (it is saved in the history as an obsolete entry) and then again comes back to the company in the result sections John is still obsolete...???!!!

Help please!!!


Your biggest problem is this:

   MERGE INTO Persons AS [Target]
USING NewPersons AS [Source]
ON Target.FirstName = Source.FirstName



Which John or Anna is matched?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Golden_eye
Golden_eye
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 228
The business key is the FirstName. I am merging the initial table with update tables and I am doing this based on the FirstName(which will not chanege in my case, only the LastName may change -for example if Anna gets married). But I don't think this is the focus of my question...Thanks
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 19524
chingarova (3/5/2013)
The business key is the FirstName. I am merging the initial table with update tables and I am doing this based on the FirstName(which will not chanege in my case, only the LastName may change -for example if Anna gets married). But I don't think this is the focus of my question...Thanks


So long as there aren't more than half a dozen or so employees in the table, that will be just Jim Dandy. A few years ago I worked for a small software house with 26 people on site, of whom 7 were called Chris (I lie - one of them was Kris). It really isn't going to work, is it? Accounting for an edge case - surname change on marriage - is completely screwing up your logic. Take the edge case out of this equation entirely, deal with them with separate code. All you need is an update!
Back to the majority. Use Firstname & Lastname as business key, it will make your merge logic much simpler.

--Create an update table to hold new/updated persons


The newpersons table would have to contain all retained persons in addition to changes, otherwise WHEN NOT MATCHED BY SOURCE clause will mark them as obsolete.


WHEN MATCHED and Target.IsRowCurrent = 1 -- SCD 1/2 changes
AND
(
Target.FirstName <> Source.FirstName


Why? They're matched on Firstname.

Personally, I'd throw this merge statement away and start again - after first rewriting the business rules.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Golden_eye
Golden_eye
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 228
OK, let me explain...
The script I posted is just an example(my real example is different and much more complex,but this doesn't matter), what I need to understand is why when we have a MERGE STATEMENT WHICH KEEPS HISTORY, WHEN AN ENTRY IS OBSOLETE AND THEN IT IS ADDED AGAIN IN THE UPDATE TABLE, IT IS NOT UPDATED IN THE MAIN TABLE AND IT IS STILL SHOWN AS OBSOLETE....if you have suggestion regarding this, it will helps. Thanks
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 19524
chingarova (3/5/2013)
OK, let me explain...
The script I posted is just an example(my real example is different and much more complex,but this doesn't matter), what I need to understand is why when we have a MERGE STATEMENT WHICH KEEPS HISTORY, WHEN AN ENTRY IS OBSOLETE AND THEN IT IS ADDED AGAIN IN THE UPDATE TABLE, IT IS NOT UPDATED IN THE MAIN TABLE AND IT IS STILL SHOWN AS OBSOLETE....if you have suggestion regarding this, it will helps. Thanks


Please don't shout.
Every successful update
WHEN MATCHED and Target.IsRowCurrent = 1 -- SCD 1/2 changes
AND
(
Target.FirstName <> Source.FirstName
OR Target.LastName <> Source.LastName
OR Target.Title <> Source.Title
)
THEN UPDATE SET


will cause a duplicate row (duplicate on firstname) to be inserted into the table, via the OUTPUT clause.

There are way too many serious logic flaws in the posted example script for it to be of any use. Can you post the actual merge statement you are using?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Golden_eye
Golden_eye
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 228
ok, lets say that the script look like this:
USE master
GO
--Create the main Persons table

CREATE TABLE Persons
(
FirstName varchar(20)
,LastName varchar (20)
,Title varchar(10)
,Email varchar (20)
,IsRowCurrent tinyint
,LastUpdated DATETIME
,ValidFrom DATETIME
,ValidTo DATETIME
)
--Populate Persons table with existing customers

INSERT INTO Persons
VALUES ('Anna','Kournikova','Miss','anna@emailme.com',1,'2012-11-01','2012-11-01','9999-12-31')
,('Roger','Federer','Mr','roger@emailme.com',1,'2012-11-01','2012-11-01','9999-12-31')
GO
--Create an update table to hold new/updated persons

CREATE TABLE NewPersons
(
FirstName varchar (20)
,LastName varchar (20)
,Title varchar(10)
,Email varchar (20)
)
--Populate People table with existing people
INSERT INTO NewPersons
VALUES ('Anna','Smith','Mrs','anna@emailme.com') --Update
,('Rafael','Nadal','Mr','roger@emailme.com') --New entry
GO
---------------------------------------------------------------------------

-- Mixed Merge - Type 1 and 2

INSERT INTO Persons
( FirstName
,LastName
,Title
,Email
,IsRowCurrent
,LastUpdated
,ValidFrom
,ValidTo
) SELECT
FirstName
,LastName
,Title
,Email
,1 --IsRowCurrent
,GETDATE() --LastUpdated
,GETDATE() --ValidFrom
,'9999-12-31' --ValidTo
FROM (
MERGE INTO Persons AS [Target]
USING NewPersons AS [Source]
ON Target.Email = Source.Email

WHEN MATCHED and Target.IsRowCurrent = 1 -- SCD 1/2 changes
AND
(
Target.FirstName <> Source.LastName
OR Target.LastName <> Source.LastName
OR Target.Title <> Source.Title
)
THEN UPDATE SET

Target.IsRowCurrent = 0 --Obsolete
,Target.LastUpdated = GETDATE()
,Target.ValidTo = GETDATE()

WHEN NOT MATCHED -- New entries
THEN INSERT (
FirstName
,LastName
,Title
,Email
,IsRowCurrent
,LastUpdated
,ValidFrom
,ValidTo
)
Values (
Source.FirstName
,Source.LastName
,Source.Title
,Source.Email
,1 --IsRowCurrent
,GETDATE() --LastUpdated
,GETDATE() --ValidFrom
,'9999-12-31' --ValidTo
)
WHEN NOT MATCHED BY SOURCE -- Obsolete removed entries
AND Target.IsRowCurrent = 1
THEN UPDATE SET
Target.IsRowCurrent = 0 --Removed
,Target.LastUpdated = GETDATE()
,Target.ValidTo = GETDATE()

OUTPUT $action AS Action
,[Source].*
) AS MergeOutput
WHERE MergeOutput.Action = 'UPDATE'
AND FirstName IS NOT NULL;
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 19524
chingarova (3/5/2013)
ok, lets say that the script look like this:


It's an improvement in that you now have a "key" (not ideal - not everybody has an email address, many folks have several) to identify individuals, but in terms of the overall functionality of the merge statement, it changes little. I honestly think it would be cheaper for you to start again from scratch, laying out the business rules first. There are a number of possible cases when you compare the two tables.
1. Match, Target.IsRowCurrent = 1
2. Match, Target.IsRowCurrent = 0
3. Person in target but not source (WHEN NOT MATCHED BY SOURCE), Target.IsRowCurrent = 1
4. Person in target but not source, Target.IsRowCurrent = 0
5. Person in source but not target
Define fully what you want to do for each case.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search