Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 merge statement & keep old record

Author  Topic 

kowalsky
Starting Member

29 Posts

Posted - 2013-04-30 : 10:16:33
hi all,
I have the following problem:
I need to merge two tables; the source contains new records and old records that may have changed. I have to update those records accordingly , however what I can't figure out how to do is keep the old record, too.

I have tried doing this:

CREATE TABLE [dbo].[table1](
[id] [int] NOT NULL,
[val1] [int] NOT NULL,
[val2] [datetime] NOT NULL,
[val3] [nvarchar](50) NOT NULL,
[val4] [int] NULL,
[val5] [nvarchar](50) NULL,
[active_flag] [nvarchar](1) NULL) ON [PRIMARY]
GO
CREATE TABLE [dbo].[table2](
[id] [int] NOT NULL,
[val1] [int] NOT NULL,
[val2] [datetime] NOT NULL,
[val3] [nvarchar](50) NOT NULL,
[val4] [int] NULL,
[val5] [nvarchar](50) NULL) ON [PRIMARY]
GO

merge into table1 as target
using table2 as source on target.id = source.id
when not matched then
insert (id, val1, val2, val3, val4, val5, active_flag)
values (source.id, source.val1, source.val2, source.val3, source.val4, source.val5, 'Y')
when matched AND
(target.val1 <> source.val1 OR
target.val2 <> source.val2 OR
target.val3 <> source.val3 OR
target.val4 <> source.val4 OR
target.val5 <> source.val5)
then
update
set target.val1 = source.val1,
target.val2 = source.val2,
target.val3 = source.val3,
target.val4 = source.val4,
target.val5 = source.val5
OUTPUT
DELETED.id,
DELETED.val1,
DELETED.val2,
DELETED.val3,
DELETED.val4,
DELETED.val5,
'N'
INTO table1(id, val1, val2, val3, val4, val5, active_flag);


but this is not working because in my example, the first two rows in the two tables are identical - this means my first DELETED.* record is null, null, null, null, null, 'N' and won't go into my table1 (nor would I want to).
How exactly am I supposed to avoid inserting the null records?
Thanks a bunch,
kowalsky

kowalsky
Starting Member

29 Posts

Posted - 2013-04-30 : 11:37:55
I do have an alternative, instead of inserting into my target where there will be at least one NOT NULL field, I can insert to a table variable with all fields NULL, and then run a cursor for this table variable and insert into my target only records with non null ids.

But I find this very brute force ...
Any ideas?

Thanks,
kowalsky
Go to Top of Page

kowalsky
Starting Member

29 Posts

Posted - 2013-04-30 : 12:32:06
I understand the requirement is wrong. The merge isn't supposed to keep the old record because if the merge is done by a PK (which is the way it should be) keeping the old record would violate the PK constraint.
Nevertheless if I still need to keep a version of the old record, using a history table is a good solution?

Thanks,
kowalsky

kowalsky
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-02 : 01:49:15
yep...you need to create a history table and using OUTPUT clause as above or using a trigger logic you can populate it with contents of the DELETED table once merge happens

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -