April 9, 2012 at 10:05 am
I was handed off some code did not do what the customer needed.
I'm tracking the Number of Inserts and Updated with a Record_Type indicator of 'I', 'U' or 'D'.
I tested the 'I' and the 'U' but not the 'D'.
I changed when not matched by source then delete;
To:
when not matched by source then update
SET t.Record_type = 'D';
I added a Record_Type Column so I could track how many records would be effected, etc before I follow up with a Delete.
Does that sound logical? Will this work?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 9, 2012 at 11:07 am
I'm assuming from the syntax that it's part of a Merge command. If so, it should work. You might need to remove the object-name ("t.") from the column you want to update. I've seen Merge throw errors for something like that. Otherwise, it looks pretty standard.
The other thing you do with Merge and Output is use the "$action" column to indicate type of action taken by the Merge command.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2012 at 11:27 am
GSquared (4/9/2012)
I'm assuming from the syntax that it's part of a Merge command. If so, it should work. You might need to remove the object-name ("t.") from the column you want to update. I've seen Merge throw errors for something like that. Otherwise, it looks pretty standard.The other thing you do with Merge and Output is use the "$action" column to indicate type of action taken by the Merge command.
merge into ctl.Payment_Trans as t using
My intent is to update records that should be deleted.
I just changed it to include the DateTimeStamp.
when not matched by source then update
SET t.Record_type = 'D'
t.Modified_Date = @Current_Date;
So can I update each record of the Output Statement to 'INSERT, 'UPDATE' or 'DETETE'?
Thanks!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 9, 2012 at 11:41 am
Here's what I've used for that kind of thing:
USE ProofOfConcept ;
GO
-- Set up table
CREATE TABLE #T (Col1 CHAR(1)) ;
INSERT INTO #T
(Col1)
VALUES ('A'),
('B') ;
GO
-- Merge Command with $action
MERGE INTO #T AS Tgt
USING
(SELECT *
FROM ( VALUES ( 'B'), ( 'D') ) AS Src (ColA)) AS Src
ON Tgt.Col1 = Src.ColA
WHEN NOT MATCHED BY TARGET
THEN INSERT (Col1)
VALUES (ColA)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT
$ACTION AS Act,
INSERTED.Col1 AS Ins_Col1,
DELETED.Col1 AS Del_Col1;
You can also Output Into or wrap an Insert Select () around the Merge statement (useful if your target table violates the rules for Output Into, which are pretty limited).
If you want to see the rows affected before-hand, you can wrap a transaction around the Merge, roll it back, and either visually inspect the Output, or have it Output into a table variable and then select from that.
Is that the kind of thing you're trying to do?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2012 at 12:10 pm
GSquared (4/9/2012)
Here's what I've used for that kind of thing:
USE ProofOfConcept ;GO
-- Set up table
CREATE TABLE #T (Col1 CHAR(1)) ;
INSERT INTO #T
(Col1)
VALUES ('A'),
('B') ;
GO
-- Merge Command with $action
MERGE INTO #T AS Tgt
USING
(SELECT *
FROM ( VALUES ( 'B'), ( 'D') ) AS Src (ColA)) AS Src
ON Tgt.Col1 = Src.ColA
WHEN NOT MATCHED BY TARGET
THEN INSERT (Col1)
VALUES (ColA)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT
$ACTION AS Act,
INSERTED.Col1 AS Ins_Col1,
DELETED.Col1 AS Del_Col1;
You can also Output Into or wrap an Insert Select () around the Merge statement (useful if your target table violates the rules for Output Into, which are pretty limited).
If you want to see the rows affected before-hand, you can wrap a transaction around the Merge, roll it back, and either visually inspect the Output, or have it Output into a table variable and then select from that.
Is that the kind of thing you're trying to do?
Thanks, in many of the table I'm dealing with millions of records.
It is very slow because the source tables come from an AS400 using a Linked Server and an OPEN QUERY.
I'm not sure how well that would work?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 9, 2012 at 12:30 pm
Probably not very well at all.
Can you stage the data from the source using some sort of delta-sensitive process? Something that just pulls over new/updated rows?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2012 at 1:05 pm
GSquared (4/9/2012)
Probably not very well at all.Can you stage the data from the source using some sort of delta-sensitive process? Something that just pulls over new/updated rows?
I'm trying to revise the code to do that.
Something that I'm not sure about is how to handle delete. Do you delete them or flag the records as having been deleted from the source?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 9, 2012 at 1:10 pm
Real deletes vs soft deletes is based on local business needs.
I've worked for companies that never deleted (or even updated) anything. It was all handled by row status indicators.
I've worked for companies that didn't bother with that and just plain update/delete as needed.
It'll depend on your local rules.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2012 at 1:14 pm
Welsh Corgi (4/9/2012)
GSquared (4/9/2012)
Probably not very well at all.Can you stage the data from the source using some sort of delta-sensitive process? Something that just pulls over new/updated rows?
I'm trying to revise the code to do that.
Something that I'm not sure about is how to handle delete. Do you delete them or flag the records as having been deleted from the source?
Thanks.
In my ODS database at a previous employer, I added three columns to the tables: An identity column, LoadBeginDate, and LoadEndDate. The most current record would have a LoadEndDate of null. I never deleted or updated data in the ODS database. If a record had any data change from the previous load, that record would have its LoadEndDate updated to the the LoadStartDate of the new record. If a record no longer existed in the source data, the LoadEndDate was also set to the LoadStartDate for that load process.
I also tracked when each load process started and stopped ina set of tables for that process. This way, for each day, I knew when the loads started and stopped. If I wanted to look at the data in a table for a specific date, I would use this information to obtain the start and stop dates used in the in-line table valued functions I had created to provide an asof picture of each table.
I also had views built on each table that should the most current data, those records whoe LoadEndDate was null.
April 9, 2012 at 1:32 pm
In addition to some routine tasks, today I created Insert_Date (with constraint), Update_Date and Record_Type (DML Operation) for 69 Tables.
I also created Indexes on the Date Columns.
My thought process was to flag the record in staging as to whether it was New (Insert), had changed (Update) or had been deleted from the source system.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 9, 2012 at 1:37 pm
Our staging database was just that, a staging database. We pulled all the data as quickly as possible from the source systems after truncating the tables in the stage database. We did absolutely no work on the data coming across. That was handled when moving the data to ODS, which is where we are going to pull all data for the actual data warehouse that we never had the time to build.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply