Using MERGE to perform the INSERT?
-
Hi,
Please see the below sample data:
create table TargetTable (Uarn INT, BACode INT, VO_Ref INT, From_Date datetime, To_Date datetime)insert into TargetTable (Uarn, BACode, VO_Ref, From_Date, To_Date)
select 906900, 1935, 121067, '2010-04-01', NULL UNION ALL
select 946003, 5249, 121041, '2012-08-06', NULL
create table SourceTable (Uarn INT, BACode INT, VO_Ref INT, From_Date datetime, To_Date datetime)
insert into SourceTable (Uarn, BACode, VO_Ref, From_Date, To_Date)
select 906900, 1935, 112067, '2013-05-12', NULL UNION ALL
select 946003, 5249, 199041, '2013-01-01', NULL
create table ResultTable (Uarn INT, BACode INT, VO_Ref INT, From_Date datetime, To_Date datetime)
insert into ResultTable (Uarn, BACode, VO_Ref, From_Date, To_Date)
select 906900, 1935, 121067, '2010-04-01', '2013-05-11' UNION ALL
select 946003, 5249, 121041, '2012-08-06', '2012-12-31' UNION ALL
select 906900, 1935, 112067, '2013-05-12', NULL UNION ALL
select 946003, 5249, 199041, '2013-01-01', NULL
select * from TargetTable
select * from sourcetable
select * from ResultTable order by 1
Is it possible to achieve this with the MERGE statement? So far my efforts have failed!
Thanks in advance.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Here is a screenshot of what I'm trying to achieve:
Would I have to use a separate statement to update/insert?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Abu Dina (8/6/2013)
Hi,Is it possible to achieve this with the MERGE statement? So far my efforts have failed!
INSERT INTO DimTableSELECT ID, Name, StartDate, null as EndDate
FROM
(
MERGE DimTable AS tgt
USING StageTable AS src
ON tgt.id = src.id AND tgt.enddate IS NULL
WHEN MATCHED THEN
UPDATE SET tgt.enddate = src.startdate
WHEN NOT MATCHED THEN
INSERT INTO (ID, Name, StartDate)
VALUES (src.id, src.name, src.startdate)
OUTPUT $action as Action, src.ID, src.Name, src.StartDate
) a
WHERE
a.Action = 'UPDATE'
Alex Suprun -
Thanks for this but I've tried to your version and it doesn't seem to work?!
I've changed your code so it uses my sample data but the syntax just looks wrong any ideas?
INSERT INTO TargetTable (Uarn, BACode, VO_Ref, From_Date, To_Date)SELECT Uarn, BACode, VO_Ref, From_Date, To_Date
FROM
(
MERGE TargetTable AS tgt
USING SourceTable AS src
ON tgt.Uarn = src.Uarn AND tgt.To_Date IS NULL
WHEN MATCHED THEN
UPDATE SET tgt.To_Date = src.From_Date
WHEN NOT MATCHED THEN
INSERT INTO TargetTable (Uarn, BACode, VO_Ref, From_Date, To_Date)
VALUES (src.Uarn, src.BACode, src.VO_Ref, src.From_Date, src.To_Date)
OUTPUT $action as Action, src.Uarn, src.BACode, src.VO_Ref, src.From_Date, src.To_Date
) a
WHERE
a.Action = 'UPDATE'
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Abu Dina (8/6/2013)
Thanks for this but I've tried to your version and it doesn't seem to work?!I've changed your code so it uses my sample data but the syntax just looks wrong any ideas?
The syntax can be slightly wrong, I didn't test the query, I just gave you an idea. You can always find a correct syntax in the documentation.
Alex Suprun -
Thanks I've sorted it out.. currently testing the results.
Yes, RTFM I know I know 😀
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Abu Dina (8/6/2013)
Thanks I've sorted it out.. currently testing the results.Yes, RTFM I know I know 😀
Would you post your solution, please?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sure!
INSERT INTO TargetTable (Uarn, BACode, VO_Ref, From_Date, To_Date)SELECT Uarn, BACode, VO_Ref, From_Date, To_Date
FROM
(
MERGE TargetTable AS tgt
USING SourceTable AS src
ON tgt.Uarn = src.Uarn AND tgt.To_Date IS NULL
WHEN MATCHED THEN
UPDATE SET tgt.To_Date = src.From_Date
WHEN NOT MATCHED THEN
INSERT (Uarn, BACode, VO_Ref, From_Date, To_Date)
VALUES (src.Uarn, src.BACode, src.VO_Ref, src.From_Date, src.To_Date)
OUTPUT $action as Action, src.Uarn, src.BACode, src.VO_Ref, src.From_Date, src.To_Date
) a
WHERE
a.Action = 'UPDATE'
I then used this and applied it to my big table but was getting error like the below:
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
What I'm trying to do is pretty simple but I'm just being stupid I think. I have a source and a target table:
As the target table is empty, both source records get loaded into the target table then we have this:
If we get a new source table like the below:
Then I would only expect one row to be added to the target table as the other one has not changed so we end up with:
I've no idea why I'm struggling with this. But I just can't get the code to work so that multiple runs of the same file doesn't produce duplicates!
Any ideas?
Thanks.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Would this work for you:
INSERT INTO TargetTable (Uarn, BACode, VO_Ref, From_Date, To_Date)SELECT Uarn, BACode, VO_Ref, From_Date, To_Date
FROM
(
MERGE TargetTable AS tgt
USING SourceTable AS src
ON tgt.Uarn = src.Uarn AND tgt.To_Date IS NULL
WHEN MATCHED AND src.VO_Ref<>tgt.VO_Ref THEN
UPDATE SET tgt.To_Date = src.From_Date
WHEN NOT MATCHED THEN
INSERT (Uarn, BACode, VO_Ref, From_Date, To_Date)
VALUES (src.Uarn, src.BACode, src.VO_Ref, src.From_Date, src.To_Date)
OUTPUT $action as Action, src.Uarn, src.BACode, src.VO_Ref, src.From_Date, src.To_Date
) a
WHERE
a.Action = 'UPDATE'
-
Thanks for this but I'm still getting this error. To be honest I don't even have to use the MERGE statement for this. I just want to be able to use some code that can be used to INSERT and UPDATE the data and for the code to handle things if the same file is loaded multiple times!
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
When I run my posted code on your sample data, everything works as expected - Nothing happens if I load the same data again.
Could you post new sample data where my code does not work as expected?
-
I don't think its a problem with the code, it's the data that I'm using it's a mess!
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply