Add table in transactional replication from script in SQL Server

Add table in transactional replication from script in SQL Server

I have already written many articles to cover different area of replication. Few days back I have written how to add articles (Table, View, SP etc.) into replication from GUI but today I am going to explain how can we insert table by script in already established transactional replication. I will be using the same transactional replication I have set up earlier.

 USE AdventureWorks2012
 GO

DECLARE @TableName VARCHAR(25)
 DECLARE @SchemaName VARCHAR(25)
 DECLARE @PublisherName VARCHAR(25)

SET @TableName='Department'
 SET @SchemaName='HumanResources'
 SET @PublisherName='AdventureWorksPublisher'

exec sp_addarticle
 @publication = @PublisherName,
 @article = @TableName,
 @source_owner = @SchemaName,
 @source_object = @TableName,
 @type = N'logbased',
 @description = null,
 @creation_script = null,
 @pre_creation_cmd = N'drop',
 @schema_option = 0x000000000803509F,
 @identityrangemanagementoption = N'manual',
 @destination_table = @TableName,
 @destination_owner = @SchemaName,
 @force_invalidate_snapshot=1
 GO

Some more replication related articles:

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

5 thoughts on “Add table in transactional replication from script in SQL Server

  1. Pingback: Add stored procedure in transactional replication by script in SQL Server

  2. MJ

    When not using snapshots, should the SP be created on both publisher and subscriber prior to running sp_addarticle?

    1. Riteshshah Post author

      SP_AddArticle stored procedure is a system stored procedure which adds SP/View/Table etc. from publisher to subscriber. whatever article you add with this SP, will be populated by its own into subscriber and any change in that article/object will be reflected to subscriber as per the replication type you have used. feel free to ask if you still have any queries or concerns.

  3. Pingback: Generate script to add multiple table in replication as article

  4. Pingback: Find undistributed replication command in SQL Server

Comments are closed.