0

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?

  • What version of SQL Server are you using? – Anthony Genovese May 31 at 19:55
  • SQL Server 2014 SP2 Enterprise Edition – Tom Jun 1 at 17:52
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.