Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Compare two Table data and insert changed field to the third table RE: Compare two Table data and insert changed field to the third table<!-- 864 -->

  • F/-\R//-\Z (8/17/2014)


    hi

    I try your code but this is not work!!!

    no detect deferent row!

    Here is the the code I previously posted with some sample data, works fine on my end;-)

    😎

    USE [tempdb]

    GO

    INSERT INTO [dbo].[BedriddenBed1]

    ([ReferralCode]

    ,[BedDate]

    ,[BedTime]

    ,[BedCode]

    ,[BedDays]

    ,[UserCode]

    ,[ISVirtual]

    ,[FirstName]

    ,[LastName]

    ,[BeneficiaryName]

    ,[ReferralDate])

    VALUES

    ('A00001','2014-01-01','22:01','B00',10,'ABC0',0,'Abcd010','Efgh001','Qwert Yuio','2013-12-31')

    ,('A00002','2014-01-02','22:02','B00',10,'ABC0',0,'Abcd020','Efgh002','Qwert Yuio','2013-12-31')

    ,('A00003','2014-01-03','22:03','B00',10,'ABC0',0,'Abcd030','Efgh003','Qwert Yuio','2013-12-31')

    ,('A00004','2014-01-04','22:04','B00',10,'ABC0',0,'Abcd040','Efgh004','Qwert Yuio','2013-12-31')

    ,('A00005','2014-01-05','22:05','B00',10,'ABC0',0,'Abcd050','Efgh005','Qwert Yuio','2013-12-31')

    ,('A00006','2014-01-06','22:06','B00',10,'ABC0',0,'Abcd060','Efgh006','Qwert Yuio','2013-12-31')

    ,('A00007','2014-01-07','22:07','B00',10,'ABC0',0,'Abcd070','Efgh007','Qwert Yuio','2013-12-31')

    ,('A00008','2014-01-08','22:08','B00',10,'ABC0',0,'Abcd080','Efgh008','Qwert Yuio','2013-12-31')

    ,('A00009','2014-01-09','22:09','B00',10,'ABC0',0,'Abcd090','Efgh009','Qwert Yuio','2013-12-31')

    ,('A00010','2014-01-10','22:10','B00',10,'ABC0',0,'Abcd100','Efgh010','Qwert Yuio','2013-12-31');

    INSERT INTO [dbo].[BedriddenBed2]

    ([ReferralCode]

    ,[BedDate]

    ,[BedTime]

    ,[BedCode]

    ,[BedDays]

    ,[UserCode]

    ,[ISVirtual]

    ,[FirstName]

    ,[LastName]

    ,[BeneficiaryName]

    ,[ReferralDate])

    VALUES

    ('A00001','2014-01-01','22:01','B00',10,'ABC0',0,'Abcd010','Efgh001','Qwert Yuio','2013-12-31')

    ,('A00002','2014-01-02','22:02','B00',10,'ABC0',0,'Abcd020','Efgh002','Qwert Yuio','2013-12-31')

    ,('A00003','2014-01-03','22:03','B00',10,'ABC0',0,'Abcd030','Efgh003','Qwert Yuio','2013-12-31')

    ,('A00004','2014-01-04','22:04','B00',10,'ABC02',0,'Abcd040','Efgh004','Qwert Yuio','2013-12-31')

    ,('A00005','2014-01-05','22:05','B00',10,'ABC02',0,'Abcd050','Efgh005','Qwert Yuio','2013-12-31')

    ,('A00006','2014-01-06','22:06','B00',10,'ABC0',0,'Abcd060','Efgh006','Qwert Yuio','2013-12-31')

    ,('A00007','2014-01-07','22:07','B00',10,'ABC0',0,'Abcd070','Efgh007','Qwert Yuio','2013-12-31')

    ,('A00008','2014-01-08','22:08','B01',10,'ABC0',0,'Abcd080','Efgh008','Qwert Yuio','2013-12-31')

    ,('A00009','2014-01-09','22:09','B01',10,'ABC0',0,'Abcd090','Efgh009','Qwert Yuio','2013-12-31')

    ,('A00010','2014-01-10','22:10','B01',10,'ABC0',0,'Abcd100','Efgh010','Qwert Yuio','2013-12-31');

    USE tempdb;

    GO

    /*

    Select records from the first table that do not match

    the second table and insert the results into the

    third table.

    */

    INSERT INTO dbo.BedriddenBed3

    (

    [ReferralCode]

    ,[BedDate]

    ,[BedTime]

    ,[BedCode]

    ,[BedDays]

    ,[UserCode]

    ,[ISVirtual]

    ,[FirstName]

    ,[LastName]

    ,[BeneficiaryName]

    ,[ReferralDate]

    )

    SELECT

    B1.[ReferralCode]

    ,B1.[BedDate]

    ,B1.[BedTime]

    ,B1.[BedCode]

    ,B1.[BedDays]

    ,B1.[UserCode]

    ,B1.[ISVirtual]

    ,B1.[FirstName]

    ,B1.[LastName]

    ,B1.[BeneficiaryName]

    ,B1.[ReferralDate]

    FROM dbo.BedriddenBed1 B1

    INNER JOIN dbo.BedriddenBed2 B2

    ON B1.ReferralCode = B2.ReferralCode

    AND B1.BedDate = B2.BedDate

    AND B1.BedTime = B2.BedTime

    WHERE B1.[BedCode] <> B2.[BedCode]

    OR B1.[BedDays] <> B2.[BedDays]

    OR B1.[UserCode] <> B2.[UserCode]

    OR B1.[ISVirtual] <> B2.[ISVirtual]

    OR B1.[FirstName] <> B2.[FirstName]

    OR B1.[LastName] <> B2.[LastName]

    OR B1.[BeneficiaryName] <> B2.[BeneficiaryName]

    OR B1.[ReferralDate] <> B2.[ReferralDate];