Microsoft SQL Server 2008 - Change Data Capture - Part 4

March 5, 2008

Change data capture is one of the many features that Microsoft is introducing in SQL Server 2008. Part 1 and Part2 of the article explored how we could enable Change Data Capture on a database and on a table. In Part 3 we discussed how to disable and enable the Change Data Capture when the table structure changes.

This article illustrates how to generate DML [Data Manipulation language – Insert, Update and Delete] statements from the history table.

Note: This article is written based on the SQL Server 2008 – Nov CTP

Step 1

Let’s create the Database “MYDB2” and the table “EMP” under the database “MyDB2” as shown below.

USE [master]
GO
/*** Object:  Database [MyDB2]   Script Date: 02/07/2008 18:46:15 ***/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB2')
DROP DATABASE [MyDB2]
GO
USE [master]
GO
/*** Object:  Database [MyDB1]   Script Date: 02/07/2008 18:46:33 ***/
CREATE DATABASE [MyDB2] 
GO
USE [MyDB2]
go
USE [MyDB2]
GO
/**** Object:  Table [dbo].[Emp]    Script Date: 02/10/2008 10:59:21 ****/
IF  EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[Emp]') AND type in (N'U'))
DROP TABLE [dbo].[Emp]
GO
USE [MyDB2]
GO
/**** Object:  Table [dbo].[Emp]    Script Date: 02/10/2008 11:02:58 ****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Emp](
 [id] [int] NULL,
 [First name] [varchar](50) NULL,
 [Last name] [varchar](50) NULL,
 [gender] [char](1) NULL,
 [Salary] [money] NULL,
 [Bonus] [money] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Step 2

Let’s enable Change Data Capture on the database “MyDB2” and on the table “EMP” as shown below.

USE [MyDB2]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO
use [MyDB2]
go
EXEC sys.sp_cdc_enable_table_change_data_capture 
@source_schema = 'dbo', 
@source_name = 'Emp', 
@role_name = 'cdc_Emp'
GO

Step 3

Now let us insert some real data to the “EMP” table, as shown below.

USE [MyDB2]
GO
insert into Emp (id, [First name],[Last name], gender,Salary, Bonus ) 
 values (1,'John', 'Smith','m', 10000, 1000)
insert into Emp (id, [First name],[Last name], gender,Salary, Bonus ) 
 values (2,'James', 'Bond','m', 20000, 2000)
insert into Emp (id, [First name],[Last name], gender,Salary, Bonus ) 
 values (3,'Alexa', 'Mantena','f', 22000, 2000)
insert into Emp (id, [First name],[Last name], gender,Salary, Bonus ) 
 values (4,'Shui', 'Qui','f', 12000, 1200)
insert into Emp (id, [First name],[Last name], gender,Salary, Bonus ) 
 values (5,'William', 'Hsu','m', 3000, 350)
insert into Emp (id, [First name],[Last name], gender,Salary, Bonus ) 
 values (6,'Danielle', 'Stewart','F', 45000, 4500)
insert into Emp (id, [First name],[Last name], gender,Salary, Bonus ) 
 values (7,'Martha', 'Mcgrath','F', 29000, 2000)
insert into Emp (id, [First name],[Last name], gender,Salary, Bonus ) 
 values (8,'Henry', 'Fayol','m', 4500, 450)
insert into Emp (id, [First name],[Last name], gender,Salary, Bonus ) 
 values (9,'Dick', 'Watson','m', 20000, 1200)
insert into Emp (id, [First name],[Last name], gender,Salary, Bonus ) 
 values (10,'Helen', 'Foster','F', 34000, 3400)
go

Step 4

Let’s Delete some data from the table “EMP” as shown below.

USE [MyDB2]
GO
Delete from Emp where id in (3,7,10)
Go

Result

home\sql2008(HOME\MAK): (3 row(s) affected)

Step 5

Now update some data from the table “EMP” as shown below.

USE [MyDB2]
GO
Update Emp set Bonus=Salary*.20 where salary >4500
GO

Result

home\sql2008(HOME\MAK): (5 row(s) affected)

Step 6

Let’s query the history table as shown below. [Fig 1.0]

USE [MyDB2]
GO
Select case __$operation when 1 then 'Deleting'
when 2 then 'Inserting'
when 3 then 'Value before Update'
when 4 then 'Value after Update'
when 5 then 'Merge' end as Operation,__$update_mask,id,[First name],[Last name], gender,Salary, Bonus
from cdc.dbo_Emp_CT
go

Result

Though the output looks readable, it can’t be used for regenerating the table from the history.

Step 7

Let’s create a small script that generates all of the DML statements for the “EMP” table, as shown below

--Generate DML Statements
set nocount on
declare @tablename varchar(128)
set @tablename='Emp'
select 'Set quoted_identifier off' as [--GenerateDDL]
select 'go' as [--GenerateDDL]
Select case __$operation when 1 then 
'Delete from '+@tablename+ 
' where id='+convert(varchar(20),id)+
' and [First Name]="'+[First Name]+'"'+
' and [Last Name]="'+[Last Name]+'"'+
' and [Gender]="'+[Gender]+'"'+
' and [Salary]='+convert(varchar(20),[Salary])+
' and [Bonus]='+convert(varchar(20),[Bonus])
when 2 then 'Insert into '+@tablename+
'(id, [First name], [Last name], gender, Salary, Bonus ) values ('+
convert(varchar(20),ID)+', "'+
[First Name]+'"'+', "'+
[Last Name]+'"'+', "'+
[Gender]+'"'+', '+
convert(varchar(20),[Salary])+', '+
convert(varchar(20),[Bonus])+')'
when 3 then 
'Delete from '+@tablename+ 
' where id='+convert(varchar(20),id)+
' and [First Name]="'+[First Name]+'"'+
' and [Last Name]="'+[Last Name]+'"'+
' and [Gender]="'+[Gender]+'"'+
' and [Salary]='+convert(varchar(20),[Salary])+
' and [Bonus]='+convert(varchar(20),[Bonus])
when 4 then 'Insert into '+@tablename+
'(id, [First name],[Last name], gender,Salary, Bonus ) values ('+
convert(varchar(20),ID)+', "'+
[First Name]+'"'+', "'+
[Last Name]+'"'+', "'+
[Gender]+'"'+', '+
convert(varchar(20),[Salary])+', '+
convert(varchar(20),[Bonus])+')'
when 5 then 'Merge' end  as [--GenerateDDL]
from cdc.dbo_Emp_CT 
order by __$start_lsn asc

When the above script is executed, it generates the Insert and Delete statement for all of the Insert, Delete and Update actions. Remember, the Update statement is actually a Delete followed by a Insert statement.

Result

--GenerateDDL
Set quoted_identifier off
--GenerateDDL
go
--GenerateDDL
Insert into Emp(id, [First name], [Last name], gender, Salary, Bonus ) values (1, "John", "Smith", "m", 10000.00, 1000.00)
Insert into Emp(id, [First name], [Last name], gender, Salary, Bonus ) values (2, "James", "Bond", "m", 20000.00, 2000.00)
Insert into Emp(id, [First name], [Last name], gender, Salary, Bonus ) values (3, "Alexa", "Mantena", "f", 22000.00, 2000.00)
Insert into Emp(id, [First name], [Last name], gender, Salary, Bonus ) values (4, "Shui", "Qui", "f", 12000.00, 1200.00)
Insert into Emp(id, [First name], [Last name], gender, Salary, Bonus ) values (5, "William", "Hsu", "m", 3000.00, 350.00)
Insert into Emp(id, [First name], [Last name], gender, Salary, Bonus ) values (6, "Danielle", "Stewart", "F", 45000.00, 4500.00)
Insert into Emp(id, [First name], [Last name], gender, Salary, Bonus ) values (7, "Martha", "Mcgrath", "F", 29000.00, 2000.00)
Insert into Emp(id, [First name], [Last name], gender, Salary, Bonus ) values (8, "Henry", "Fayol", "m", 4500.00, 450.00)
Insert into Emp(id, [First name], [Last name], gender, Salary, Bonus ) values (9, "Dick", "Watson", "m", 20000.00, 1200.00)
Insert into Emp(id, [First name], [Last name], gender, Salary, Bonus ) values (10, "Helen", "Foster", "F", 34000.00, 3400.00)
Delete from Emp where id=3 and [First Name]="Alexa" and [Last Name]="Mantena" 
 and [Gender]="f" and [Salary]=22000.00 and [Bonus]=2000.00
Delete from Emp where id=7 and [First Name]="Martha" and [Last Name]="Mcgrath" 
 and [Gender]="F" and [Salary]=29000.00 and [Bonus]=2000.00
Delete from Emp where id=10 and [First Name]="Helen" and [Last Name]="Foster" 
 and [Gender]="F" and [Salary]=34000.00 and [Bonus]=3400.00
Delete from Emp where id=1 and [First Name]="John" and [Last Name]="Smith" and [Gender]="m" and [Salary]=10000.00 and [Bonus]=1000.00
Insert into Emp(id, [First name],[Last name], gender,Salary, Bonus ) values (1, "John", "Smith", "m", 10000.00, 2000.00)
Delete from Emp where id=2 and [First Name]="James" and [Last Name]="Bond" and [Gender]="m" and [Salary]=20000.00 and [Bonus]=2000.00
Insert into Emp(id, [First name],[Last name], gender,Salary, Bonus ) values (2, "James", "Bond", "m", 20000.00, 4000.00)
Delete from Emp where id=4 and [First Name]="Shui" and [Last Name]="Qui" and [Gender]="f" and [Salary]=12000.00 and [Bonus]=1200.00
Insert into Emp(id, [First name],[Last name], gender,Salary, Bonus ) values (4, "Shui", "Qui", "f", 12000.00, 2400.00)
Delete from Emp where id=6 and [First Name]="Danielle" and [Last Name]="Stewart" 
 and [Gender]="F" and [Salary]=45000.00 and [Bonus]=4500.00
Insert into Emp(id, [First name],[Last name], gender,Salary, Bonus ) values (6, "Danielle", "Stewart", "F", 45000.00, 9000.00)
Delete from Emp where id=9 and [First Name]="Dick" and [Last Name]="Watson" and [Gender]="m" and [Salary]=20000.00 and [Bonus]=1200.00
Insert into Emp(id, [First name],[Last name], gender,Salary, Bonus ) values (9, "Dick", "Watson", "m", 20000.00, 4000.00)

Conclusion

Based on the value of the column __$operation we can create scripts that generate DML [Data Manipulation language – Insert, Update and Delete] statements from the history table.

» See All Articles by Columnist MAK