CODEWITHSUNDEEP
sql-serverreplication
John Hunter
John Hunter

Reputation: 4142

How to add a constant column when replicating a database?

I am using SQL Server 2000 and I have two databases that both replicate (transactional push subscription) to a single database. I need to know which database the records came from.

So I want to add a fixed column specified in the publication to my table so I can tell which database the row originated from.

How do I go about doing this?

I would like to avoid altering the main databases mostly due to the fact there are many tables I would need to do this to. I was hoping for some built in feature of replication that would do this for me some where. Other than that I would go with the view idea.

Upvotes: 1

Views: 746

Answers (3)

John Hunter
John Hunter

Reputation: 4142

So the solution for me was to set up the replication publications to allow transformations and create a DTS package for each site that appends the siteid into the tables to keep the ids unique as I can't use guids.

Upvotes: 0

Chris Shaffer
Chris Shaffer

Reputation: 32575

You could use a calculated column Use the following on the two databases:


ALTER TABLE TableName ADD
    MyColumn AS 'Server1'

Then just define the single "master" database to use a VARCHAR column (or whatever you want) that you fill using the calculated columns value.

Upvotes: 1

Sunny Milenov
Sunny Milenov

Reputation: 22310

You can create a view, which adds the "constant" column, and use it as a replication source.

Upvotes: 0

Related Questions