CDC and BIML Preparation of Database
--1. Create database | |
CREATE DATABASE CDCTest; | |
GO | |
--2. Create CDC table using AdventureWorksDW2012 database | |
USE CDCTest | |
GO | |
SELECT * | |
INTO DimDepartmentGroup_CDC | |
FROM AdventureWorksDW2012..DimDepartmentGroup | |
WHERE DepartmentGroupKey < 5 | |
--3. Enable cdc on this database | |
USE CDCTest | |
GO | |
EXEC sys.sp_cdc_enable_db | |
GO | |
--4. Add a primary key to the DimDepartmentGroup_CDC table so we can enable support for net changes | |
USE CDCTest | |
GO | |
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N'PK_DimDepartmentGroup_CDC') | |
ALTER TABLE dbo . DimDepartmentGroup_CDC ADD CONSTRAINT PK_DimDepartmentGroup_CDC PRIMARY KEY CLUSTERED | |
( | |
DepartmentGroupKey ASC | |
) | |
GO | |
--5. Enable cdc on the DimDepartmentGroup_CDC table | |
USE CDCTest | |
GO | |
EXEC sys.sp_cdc_enable_table | |
@source_schema = N'dbo', | |
@source_name = N'DimDepartmentGroup_CDC', | |
@role_name = N'cdc_admin', | |
@supports_net_changes = 1 | |
GO | |
--6. Create a destination table in the | |
USE CDCTest | |
GO | |
SELECT TOP 0 * | |
INTO DimDepartmentGroup_Destination | |
FROM DimDepartmentGroup_CDC | |
--7. Create table to store the cdc state | |
USE CDCTest | |
GO | |
CREATE TABLE dbo.cdc_states( | |
name nvarchar(256) NOT NULL, | |
state nvarchar(256) NOT NULL | |
) | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment