replicatingdata to tablesof different column names

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

replicatingdata to tablesof different column names

Author

Topic

BajiPrasad
Starting Member

3 Posts

Posted - 2007-04-18 : 01:58:43

hi,

is it possible to replicate data from one table to another which have different names,different schemas and different column names.

Im trying this using transactional replication.

i achieved this for different schema, different table names.but it is failing for different column names.

till now i haven't got a proper reply for my problem.

p.s : this is specially for tara,goddess of Sql..

Baji Prasad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-18 : 13:42:37

Yes you can do that. By default, replication uses stored procedures to move the data. So setup replication and pretend you are going to exact same table structure at the subscriber. Then modify the replication stored procedures to fit your needs. The stored procedures exist in the publisher's database and are named like this:

sp_MS<DML>_objectOwnerobjectName

<DML> can be upd, ins, or del.

Here is an example of one of ours:
sp_MSupd_dbomessages

You will need to source control these stored procedures so that you don't lose the changes if you ever setup replication again and forget to set the option to false to not create these stored procedures.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

BajiPrasad
Starting Member

3 Posts

Posted - 2007-04-19 : 03:29:24

Hi tara..you really proved that ur the sql goddess...

but let me tell im already using the same approach what you told to me.

before going to the problem ...let me explain my scenario perfectly.

my aim is to replicate data from publisher table to subscriber table.

Publication Name :TestPubVer

Publisher Table-->CustomerOrder

----------------------

CustomerID

CustomerName

CustomerAddress

Subscriber Table--> Consumer

--------------------

ConsumerID

ConsumerName

ConsumerAddress

I achieved the above upto different table names but same column names.
when ever i create a susbscription , will create a article for that,
where i will specify a table name and columns.

once it was done. i will create a subscription for that corresponding publication.
when the snapshot agent is started.it will do bulk copy to the subscriber table.
so definitely i have to use same column names.
after bulk copy only. i can create procedures for insert,delete ..and can customize. for which i didn't get a problem.
i hope u understood my problem.

Regards
Baji Prasad.B
future SQL King

Baji Prasad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-19 : 11:31:10

Do you have a question? Or is everything working now?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

BajiPrasad
Starting Member

3 Posts

Posted - 2007-04-20 : 01:23:37

hi tara...thanks for ur interest ..
ya replication is working for different tables and different schema.
but its not working for different column names.
currently im working on it.
i hope u too help me regarding this.

Regards
Baji Prasad.B

quote:
Originally posted by tkizer

Do you have a question? Or is everything working now?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Baji Prasad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-20 : 12:50:17

You will need to explain what isn't working in order for us to help.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources