Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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