SQL Server CDC (Change Data Capture) Existing Data Seed

Question

I have an SQL Server Database with 100's of tables with as many rows in each and I'm turning on CDC for some reporting. Since data was created/inserted BEFORE turning on CDC, the CDC tables are current empty and I would like to get the data into the CDC tables to start reporting.

Is there a way to 'refresh' or seed the existing data into the CDC tables?

---

I've tried a simple update with existing data, however this doesn't actually cause a CDC entry. Presumably it isn't writing to the transaction log and triggering an write.

UPDATE table1 SET column1 = column1;

In my case, most of my tables currently have an UPDATEDON column which I could just update to a now timestamp, which will cause an update and subsequent entries in the CDC tables, but I'd prefer to retain the existing update time.

UPDATE table1 set UPDATEDON = getDateUtc();

Alternatively I could go through and move all the data into temporary tables, delete the data and re-insert it to cause updates, but surely there is a better way? How have others tackled this?

Answer 1

In case others end up here, a solution that works for me was to

1. store data in a temp table
2. delete source table data
3. delete cdc table data
4. insert temp table back into source table

I had to build up a list of column names in order to insert the IDENTITY which accounts for most of the script. Below is similar to what I used, however I was running inside a loop of all of my tables.

-- 1. Store data
SELECT * INTO #refreshTable FROM dbo.myTable;

-- 2. delete source table data
DELETE FROM dbo.myTable;

-- 3. delete cdc table data
DELETE FROM cdc.dbo_myTable_CT;

-- Build list of columns for insert
DECLARE @MyCursor CURSOR;
DECLARE @MyField nvarchar(max);
DECLARE @columns nvarchar(max) = '';
BEGIN
    SET @MyCursor = CURSOR FOR
        select [name] from tempdb.sys.columns where object_id = object_id('tempdb..#refreshTable');

    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @columns = CONCAT(@columns, ', ', @MyField)
        FETCH NEXT FROM @MyCursor 
        INTO @MyField
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;
SET @columns = SUBSTRING(@columns, 2, 10000); -- Not expecting long column names

-- 4. Insert data back into source table
SET IDENTITY_INSERT dbo.myTable ON;
DECLARE @sql nvarchar(MAX) = 'INSERT INTO dbo.myTable (' + @columns + ') SELECT * FROM #refreshTable';
EXEC (@sql);
SET IDENTITY_INSERT dbo.myTable OFF;