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.

[sourcecode language=”sql”]
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[/sourcecode]

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.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

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

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

    1. 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.

Comments are closed.