/*

-- =============================================

-- 创建测试数据库及数据表，借助AdventureWorksDW2012示例数据库

---Generate By downmoon(邀月),3w@live.cn

-- =============================================

*/

--Create database CDCTest

--GO

--USE [CDCTest]

--GO

--SELECT * INTO DimCustomer_CDC

--FROM [AdventureWorksDW2012].[dbo].[DimCustomer]

--WHERE CustomerKey < 11500;

--select * from DimCustomer_CDC;

/*

-- =============================================

-- 启用数据库级别CDC，只对企业版和开发版有效

---Generate By downmoon(邀月),3w@live.cn

-- =============================================

*/

USE

 [CDCTest]

GO

EXEC sys.sp_cdc_enable_db

GO

-- add a primary key to the DimCustomer_CDC table so we can enable support for net changes

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id =

OBJECT_ID(N'[dbo].[DimCustomer_CDC]') AND name = N'PK_DimCustomer_CDC')

  ALTER TABLE [dbo].[DimCustomer_CDC] ADD CONSTRAINT

[PK_DimCustomer_CDC] PRIMARY KEY CLUSTERED

(

    [CustomerKey] ASC

)

GO

/*

-- =============================================

-- 启用表级别CDC

---Generate By downmoon(邀月),3w@live.cn

-- =============================================

*/

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name = N'DimCustomer_CDC',

@role_name = N'cdc_admin',

@supports_net_changes = 1

GO

/*

-- =============================================

-- 创建一个目标表，与源表(Source)有相同的表结构

--注意，在生产环境中，完全可以是不同的实例或服务器，本例为了方便，在同一个数据库实例的同一个数据库中演示

---Generate By downmoon(邀月),3w@live.cn

-- =============================================

*/

SELECT TOP 0 * INTO DimCustomer_Destination

FROM DimCustomer_CDC

--select @@version;

select * from DimCustomer_Destination;

-- =============================================

-- 我们使用两个包来完成示例，一个初始包完成数据的初始加载，一个增量包完成数据的变更捕获

---Generate By downmoon(邀月),3w@live.cn

-- =============================================

--Create stage Tables

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stg_DimCustomer_UPDATES]') AND type in (N'U'))

BEGIN

   SELECT TOP 0 * INTO stg_DimCustomer_UPDATES

   FROM DimCustomer_Destination

END

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stg_DimCustomer_DELETES]') AND type in (N'U'))

BEGIN

   SELECT TOP 0 * INTO stg_DimCustomer_DELETES

   FROM DimCustomer_Destination

END

-- batch update

UPDATE dest

SET

    dest.FirstName = stg.FirstName,

    dest.MiddleName = stg.MiddleName,

    dest.LastName = stg.LastName,

    dest.YearlyIncome = stg.YearlyIncome

FROM

    [DimCustomer_Destination] dest,

    [stg_DimCustomer_UPDATES] stg

WHERE

    stg.[CustomerKey] = dest.[CustomerKey]

-- batch delete

DELETE FROM [DimCustomer_Destination]

  WHERE[CustomerKey] IN

(

    SELECT [CustomerKey]

    FROM [dbo].[stg_DimCustomer_DELETES]

)

-- truncate table

truncate table  [dbo].[stg_DimCustomer_DELETES]

truncate table  [dbo].[stg_DimCustomer_UPDATES]

-- =============================================

-- 更新一些数据,以显示SSIS 2012中CDC的效果

---Generate By downmoon(邀月),3w@live.cn

-- =============================================

USE [CDCTest]

GO

-- Transfer the remaining customer rows

SET IDENTITY_INSERT DimCustomer_CDC ON

INSERT INTO DimCustomer_CDC

(

       CustomerKey, GeographyKey, CustomerAlternateKey, Title, FirstName,

       MiddleName, LastName, NameStyle, BirthDate, MaritalStatus,

       Suffix, Gender, EmailAddress, YearlyIncome, TotalChildren,

       NumberChildrenAtHome, EnglishEducation, SpanishEducation,

       FrenchEducation, EnglishOccupation, SpanishOccupation,

       FrenchOccupation, HouseOwnerFlag, NumberCarsOwned, AddressLine1,

       AddressLine2, Phone, DateFirstPurchase, CommuteDistance

)

SELECT CustomerKey, GeographyKey, CustomerAlternateKey, Title, FirstName,

       MiddleName, LastName, NameStyle, BirthDate, MaritalStatus,

       Suffix, Gender, EmailAddress, YearlyIncome, TotalChildren,

       NumberChildrenAtHome, EnglishEducation, SpanishEducation,

       FrenchEducation, EnglishOccupation, SpanishOccupation,

       FrenchOccupation, HouseOwnerFlag, NumberCarsOwned, AddressLine1,

       AddressLine2, Phone, DateFirstPurchase, CommuteDistance

FROM [AdventureWorksDW2012].[dbo].[DimCustomer]

WHERE CustomerKey =11502

SET IDENTITY_INSERT DimCustomer_CDC OFF

GO

-- give 10 people a raise

UPDATE DimCustomer_CDC

SET

    YearlyIncome = YearlyIncome + 10

WHERE

    CustomerKey >= 11000 AND CustomerKey <= 11010

GO