Add a table to an Existing Transactional Replication using T-SQL and Generate Snapshot using T-SQL

Replace the PublishedDB with your published database and TableName with the table name you want to replicate

-- Adding TableName to Publication
use [PublishedDB]

exec sp_addarticle
@publication = N'PublicationName',
@article = N'TableName',
@source_owner = N'dbo',
@source_object = N'TableName',
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'truncate',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'manual',
@destination_table = N'TableName',
@destination_owner = N'dbo',
@status = 24,
@vertical_partition = N'false',
@ins_cmd = N'CALL [sp_MSins_dboTableName]',
@del_cmd = N'CALL [sp_MSdel_dboTableName]',
@upd_cmd = N'SCALL [[sp_MSupd_dboTableName]'

The next step is to start the snapshot agent to pick up the new tables added to replication and replicate across to Subscriber

Replace the PublicatioName with your existing publication name.

-- Start the Snapshot Agent job.
USE [PublishedDB]
EXEC sp_startpublication_snapshot @publication = 'publicationName';

This entry was posted in Replication, SQL Server, T-SQL Scripts, Transactional Replication, Tricks. Bookmark the permalink.

Leave a Reply