incremental data load

--prepare table
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'dbo.MyTest')
                    AND type IN ( N'U' ) )
    DROP TABLE dbo.MyTest
GO

CREATE TABLE dbo.MyTest
    (
      myKey INT NOT NULL ,
      myValue INT NULL ,
      myValueText VARCHAR(10) NULL ,
      hash AS ( hashbytes('SHA1', ISNULL(CONVERT(VARCHAR(MAX), MyValue, 0),'') + ISNULL(CONVERT(VARCHAR(MAX), myValueText, 0),'')) ) ,
      CONSTRAINT PK_MyTest PRIMARY KEY CLUSTERED ( myKey ASC )
    )

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'dbo.MyTestSource')
                    AND type IN ( N'U' ) )
    DROP TABLE dbo.MyTestSource
GO

CREATE TABLE dbo.MyTestSource
    (
      myKey INT NOT NULL ,
      myValue INT NULL ,
      myValueText VARCHAR(10) NULL,
    )

GO

--prepare data
INSERT INTO dbo.MyTestSource(myKey, myValue, myValueText)
SELECT 1, 1, 'A' UNION ALL
SELECT 2, -2, '-B' UNION ALL    --data changed
--SELECT 3, 3 UNION ALL         --data removed
SELECT 4, 4, 'D'                --data added


INSERT INTO dbo.MyTest(myKey, myValue, myValueText)
SELECT 1, 1, 'A' UNION ALL
SELECT 2, 2, 'B' UNION ALL
SELECT 3, 3, 'C'

--check data
SELECT * FROM MyTestSource;
SELECT * FROM MyTest;
------------------------------------------------------------
--increamental sych data, delete, update and then append
------------------------------------------------------------
--delete
DELETE  MT
FROM    MyTest AS MT
WHERE   myKey NOT IN ( SELECT   myKey
                       FROM     MyTestSource AS MTS ) ;

--update
UPDATE  MT
SET     MT.MyValue = MTS.MyValue ,
        MT.MyValueText = MTS.MyValueText
FROM    MyTest AS MT
        INNER JOIN MyTestSource AS MTS ON MT.myKey = MTS.myKey
                                          AND MT.hash <> ( hashbytes('SHA1', ISNULL(CONVERT(VARCHAR(MAX), MTS.MyValue, 0),'') + ISNULL(CONVERT(VARCHAR(MAX), MTS.myValueText, 0),'')) ) ;
--append          
INSERT  INTO MyTest
        ( myKey ,
          myValue ,
          myValueText
        )
        SELECT  myKey ,
                myValue ,
                myValueText
        FROM    MyTestSource
        WHERE   myKey IN ( SELECT   myKey
                           FROM     MyTestSource AS MTS
                           EXCEPT
                           SELECT   myKey
                           FROM     MyTest AS MT ) ;
                                   
--check the reulst
SELECT * FROM MyTest;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29351/viewspace-669722/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29351/viewspace-669722/