Databases
StackOverflow2013
Postsdbo
POSql Server Change Data Capture: Preserving history when adding columns?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSql Server Change Data Capture: Preserving history when adding columns?
    primarykey
    data
    text
    <p>When a new column is added to table that is configured for change data capture (cdc), the capture instance table will not have the new column until cdc is disabled and re-enabled for the source table. In the process the existing capture instance is dropped.</p> <p>I thought I could copy existing data out to a temp table and then copy back using the following SQL. However, other CDC meta information, such as the cdc.change_tables.start_lsn, becomes invalid.</p> <p>How can the capture instance history be preserved, using the same capture instance name, if at all?</p> <p>Thanks, Rich</p> <pre><code>/*Change Data Capture Test - Alter table definition test */ /*Enter restricted mode so we don't lose data changes during this process*/ alter database ChangeDataCaptureTest set AUTO_UPDATE_STATISTICS_ASYNC OFF alter database ChangeDataCaptureTest set RESTRICTED_USER with ROLLBACK IMMEDIATE go /*Add a column to the table*/ alter table dbo.Table1 add value3 varchar(20) DEFAULT '' not null /*Copy the existing change tracking into a temp table*/ select * into cdc.dbo_Table1_temp from cdc.dbo_Table1_CT /*Add the new column to the temp table so that we don't have to map all columns when we copy back, note that we use NULL as the default*/ alter table cdc.dbo_Table1_temp add value3 varchar(20) DEFAULT NULL /*Disable CDC on the source table, this will drop the associated cdc table*/ exec sys.sp_cdc_disable_table @source_schema='dbo', @source_name='Table1', @capture_instance='dbo_Table1' /*Enable CDC for the table which recreates the CDC table*/ EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Table1', @role_name = NULL, @supports_net_changes = 1, @filegroup_name = N'ChangeDataCapture' GO /*Insert values from the temp table back into the new CDC Table*/ Insert into cdc.dbo_Table1_CT SELECT * From cdc.dbo_Table1_temp go /*Drop the temp table*/ drop table cdc.dbo_Table1_temp /*Go back into multi-user mode*/ alter database ChangeDataCaptureTest set AUTO_UPDATE_STATISTICS_ASYNC ON alter database ChangeDataCaptureTest set MULTI_USER go /*Add a new row to the table*/ insert into table1 values(12,'zz','g') </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USRichard Collette
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSql Server Change Data Capture: Preserving history when adding columns?
      1. USDerek Morrison
      1. VTFavorite
    2. VO
      singulars
      1. POSql Server Change Data Capture: Preserving history when adding columns?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSql Server Change Data Capture: Preserving history when adding columns?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou probably cannot have them back in the same table. Copy them into a new table. While retrieving reports, 'union' that table to get the previous records.
      singulars
      1. POSql Server Change Data Capture: Preserving history when adding columns?
      1. USAnkur-m
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload