SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 merge statement & keep old record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kowalsky
Starting Member

USA
27 Posts

Posted - 04/30/2013 :  10:16:33  Show Profile  Reply with Quote
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

USA
27 Posts

Posted - 04/30/2013 :  11:37:55  Show Profile  Reply with Quote
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

USA
27 Posts

Posted - 04/30/2013 :  12:32:06  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 05/02/2013 :  01:49:15  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000