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]GOCREATE 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]GOmerge into table1 as targetusing 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