I think you're right.
maybe like this:
DECLARE @ETL_HISTORY TABLE(
[Action] [varchar](50) NULL,
[New_FirstName] [nvarchar](50) NULL,
[New_MiddleName] [nvarchar](50) NULL,
[New_LastName] [nvarchar](50) NULL,
[New_EmailAddress] [nvarchar](50) NULL,
[Old_FirstName] [nvarchar](50) NULL,
[Old_MiddleName] [nvarchar](50) NULL,
[Old_LastName] [nvarchar](50) NULL,
[Old_EmailAddress] [nvarchar](50) NULL)--,
MERGE DBO.Contact T
USING Person.Contact S
ON T.EmailAddress = S.EmailAddress
WHEN MATCHED AND
T.FirstName <> S.FirstName OR
T.MiddleName <> S.MiddleName OR
T.LastName <> S.LastName
T.FirstName = S.FirstName,
T.MiddleName = S.MiddleName,
T.LastName = S.LastName
WHEN NOT MATCHED THEN INSERT
WHEN NOT MATCHED BY SOURCE THEN
OUTPUT $action, inserted.*, deleted.* INTO @ETL_HISTORY;
SELECT * FROM @ETL_HISTORY
Right there with Babe
I am eternally grateful for Stairway series. But if someone points out a typo like the "Person.Contact" / dbo.Contact" issue, you should fix it. This happened in an earlier lesson in the series as well, and it can take those of us following along an hour or so to figure out what in the hell went wrong. I finally just thought to look in the comments for the corrections.
Just a suggestion. But once again, thank you for taking the time to create these! Great job.
I really appreciate the complete and easy to follow elaboration on these series.
I have a question regarding the control flow. what I realize from the series of incremental load is that, the "Insert and update rows" data flow is concerned with detecting the changing records and update them based on conditions which we provided.
on the other hand, the " Delete rows" data flow task is related to delete rows in the destination which are no longer exist in the source.
So what I miss here is the "Apply staged updates" in the control flow. what I comprehend is that the "update" job is done in the "Insert and update rows" data flow task. so what is the responsibility of the "Apply staged updates" here?
I would be grateful if you could articulate this a bit more.
Thanks in advance.
the insert-update block does not do the update itself - just inserts the rows that need to be updated onto a a staging table, and then the "apply staged updates" step does a single update statement from that table.
Look at the level 4 link which explains this in detail - from a performance point of view its normally better to do the updates and also the deletes in a set based manner instead of using a oledb command to do one at the time.
Thanks for your reply. You were right. I checked the level 4 once again and that solved my problem.
Viewing 6 posts - 16 through 20 (of 20 total)