Mimicking MERGE Statement in SQL Server 2005
Suppose you have a table
create table permanent(id int, d float, comment varchar(15))
go
insert into permanent values(1, 10., 'Original Row')
insert into permanent values(2, 10., 'Original Row')
insert into permanent values(3, 10., 'Original Row')
go
And you want to modify it with incoming values from a staging table
create table staging(id int, d float)
go
insert into staging values(2, 15.)
insert into staging values(3, 15.)
insert into staging values(4, 15.)
go
You want to modify rows 2 and 3 and to add a new row 4. In DB2 and oracle there is a very convenient MERGE statement which does precisely this. In SQL Server 2005 you can use OUTPUT clause of an UPDATE statement:
go
declare @updated_ids table(id int)
update permanent set d=s.d, comment = 'Modified Row'
output inserted.id into @updated_ids
from permanent p, staging s
where p.id=s.id
insert into permanent
select id, d, 'New Row' from staging where id not in(select id from @updated_ids)
go
select * from permanent
go
id d comment
----------- ---------------------- ---------------
1 10 Original Row
2 15 Modified Row
3 15 Modified Row
4 15 New Row
posted by Alex Kuznetsov @ 6:50 PM
11 comments
11 Comments:
Hey this would be great!
What does "d" represent? Could you provide an example for tables that employ a composite key? For example if the two tables you want to merge each have three columns that make up the composite key, how would this merge statement be written?
Brian,
Here you go:
create table permanent(i1 int, i2 int, i3 int, d float, comment varchar(15),
CONSTRAINT Permanent_PK PRIMARY KEY(i1, i2, i3))
go
insert into permanent values(1, 10, 100, 11., 'Original Row')
insert into permanent values(2, 20, 200, 12., 'Original Row')
insert into permanent values(3, 30, 300, 13., 'Original Row')
go
--And you want to modify it with incoming values from a staging table
create table staging(i1 int, i2 int, i3 int, d float)
go
insert into staging values(2, 20, 200, 15.)
insert into staging values(3, 30, 300, 15.)
insert into staging values(4, 40, 400, 15.)
go
--You want to modify rows 2 and 3 and to add a new row 4. In DB2 and oracle there is a very convenient MERGE statement which does precisely this. In SQL Server 2005 you can use OUTPUT clause of an UPDATE statement:
go
select * from permanent
/*
i1 i2 i3 d comment
----------- ----------- ----------- ---------------------- ---------------
1 10 100 11 Original Row
2 20 200 12 Original Row
3 30 300 13 Original Row
*/
go
declare @updated_ids table(i1 int, i2 int, i3 int);
update permanent set d=s.d, comment = 'Modified Row'
output inserted.i1, inserted.i2, inserted.i3 into @updated_ids
from permanent p, staging s
where p.i1=s.i1 AND p.i2=s.i2 AND p.i3=s.i3;
insert into permanent
select i1, i2, i3, d, 'New Row' from staging where not exists(select 1 from @updated_ids i
WHERE i.i1 = staging.i1 AND i.i2 = staging.i2 AND i.i3 = staging.i3);
/*
i1 i2 i3 d comment
----------- ----------- ----------- ---------------------- ---------------
1 10 100 11 Original Row
2 20 200 15 Modified Row
3 30 300 15 Modified Row
4 40 400 15 New Row
*/
go
select * from permanent
go
DROP TABLE permanent
go
DROP TABLE Staging
go
This is ingenious!! Very very useful.
Nice work- and thank you so much.
How would you mimick MERGE for Common table expression (CTE which builds PIVOT result)?
MERGE statement is only relevant to modifications. To merge two result sets, I would use FULL OUTER JOIN.
Hi
Does any one get this kind exception using this query:
An explicit value for the identity column in table 'xxxxxxx' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Daniel
http://msdn.microsoft.com/en-us/library/ms188059.aspx
USE AdventureWorks;
GO
-- Create tool table.
CREATE TABLE dbo.Tool(
ID INT IDENTITY NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL
)
GO
-- Inserting values into products table.
INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver')
INSERT INTO dbo.Tool(Name) VALUES ('Hammer')
INSERT INTO dbo.Tool(Name) VALUES ('Saw')
INSERT INTO dbo.Tool(Name) VALUES ('Shovel')
GO
-- Create a gap in the identity values.
DELETE dbo.Tool
WHERE Name = 'Saw'
GO
SELECT *
FROM dbo.Tool
GO
-- Try to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO
-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
SELECT *
FROM dbo.Tool
GO
-- Drop products table.
DROP TABLE dbo.Tool
GO
Thanks this is a super-useful solution. I particulary like the ability to audit row updates. Your pattern provides for a very suitable method to track the row changes with a timestamp. This can be achieved by replace your "Comment" field with a datetime fild and perhaps adding a second fild called say, "CreatedDate" making the time the row was first added.
Nice succint example. Thanks. I don't like your casing style, but beggars can't be choosers :)
Thank you for your posting. This is super- useful. Thank you so much.
Thanks much man! You saved my day. I was working on a critical client project and was looking for such a solution. This was perfect. Keep posting such solutions, these tips are really helpful for developer like me.:)
Post a Comment
<< Home