How to Synchronize two databases

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

Transact-SQL (2005)

How to Synchronize two databases

Author

Topic

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-12 : 00:49:30

Hi, I have two databases having one table each. i want to synchrnize these two DB's.
.i.e. If i update/insert/delete any data on Tbl_Names in Database DB1, automatically the effect
should take place in Tbl_Data in Database DB2


Database Name :DB1
Table Name: Tbl_Names

FName     LName      Addr
Satish    Gorijala    Hyd
John      Smith       US
Murali    Mutiah      SL
Chu       Chin        China


Database Name :DB2
Table Name: Tbl_Data

KName   GName     Country
Satish     Gorijala    Hyd
John       Smith       US
Murali     Mutiah      SL
Chu        Chin        China

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-12 : 01:13:54

DB1 and DB2 are on the same server?
That means only one way?
Changes are always made in DB1?
So DB2 is used only to read data?

No, you're never too old to Yak'n'Roll if you're too young to die.

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-12 : 01:37:15

Yes, DB1 & DB2 are in same server
Changes take place alwasy from 1 side(i.e from DB1)
i.e DB2 is ready only...

quote:
Originally posted by webfred

DB1 and DB2 are on the same server?
That means only one way?
Changes are always made in DB1?
So DB2 is used only to read data?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-12 : 01:47:35

Ok I think a trigger can do that without any problems.
But did you show all columns in example above?
There is no Key that you can use to identify a record like User_ID or something else?

No, you're never too old to Yak'n'Roll if you're too young to die.

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-12 : 02:28:29

Yes, i showed all columns. There is no keys here
If Db2 is at another server, triggers wont work?

quote:
Originally posted by webfred

Ok I think a trigger can do that without any problems.
But did you show all columns in example above?
There is no Key that you can use to identify a record like User_ID or something else?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-12 : 02:40:28

I would never write a trigger which has to work in relation to another server.
In this case I would think about REPLICATION. But REPLICATION needs a Key in the source-table.

No, you're never too old to Yak'n'Roll if you're too young to die.

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-12 : 02:46:35

Ok just for information i asked that if DB2 is at another server.
But in my requirement both Databases are in same server. I am not much aware of triggers. Can you give syntax of by showing an example how a row is inserted in DB2 if it is inserted in DB1. I tried by starting ..but not getting idea how to take another Db name in trigger.

CREATE TRIGGER trig_addrow
ON Tbl_Names
FOR INSERT
AS

quote:
Originally posted by webfred

I would never write a trigger which has to work in relation to another server.
In this case I would think about REPLICATION. But REPLICATION needs a Key in the source-table.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-12 : 02:59:26

In case of INSERT the trigger has a table called inserted.
You can now insert all rows from inserted into the table in DB2 like this:

insert DB2.dbo.TBL_Data
select * from inserted

No, you're never too old to Yak'n'Roll if you're too young to die.

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-12 : 03:38:02

If i use this statemetn, it will insert all rows from Inserted table to Tbl_data

But my requirement is if i insert one row in Db1. Tbl_Names, then that row only need to insert in DB2.Tbl_Data

quote:
Originally posted by webfred

In case of INSERT the trigger has a table called inserted.
You can now insert all rows from inserted into the table in DB2 like this:

insert DB2.dbo.TBL_Data
select * from inserted

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-12 : 03:43:14

quote:
Originally posted by Mng

If i use this statemetn, it will insert all rows from Inserted table to Tbl_data

But my requirement is if i insert one row in Db1. Tbl_Names, then that row only need to insert in DB2.Tbl_Data

quote:
Originally posted by webfred

In case of INSERT the trigger has a table called inserted.
You can now insert all rows from inserted into the table in DB2 like this:

insert DB2.dbo.TBL_Data
select * from inserted

No, you're never too old to Yak'n'Roll if you're too young to die.

For that either you need to maintain auto incremented id or date to get the latest record inserted in the table.

Vabhav T

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-12 : 03:52:50

Ya, if i have identity(auto incrment) column...how can i get only latest inserted record in to another table using trigger.

Please Can you give me the syntax for this?

quote:
Originally posted by vaibhavktiwari83

quote:
Originally posted by Mng

If i use this statemetn, it will insert all rows from Inserted table to Tbl_data

But my requirement is if i insert one row in Db1. Tbl_Names, then that row only need to insert in DB2.Tbl_Data

quote:
Originally posted by webfred

In case of INSERT the trigger has a table called inserted.
You can now insert all rows from inserted into the table in DB2 like this:

insert DB2.dbo.TBL_Data
select * from inserted

No, you're never too old to Yak'n'Roll if you're too young to die.

For that either you need to maintain auto incremented id or date to get the latest record inserted in the table.

Vabhav T

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-12 : 03:53:48

No it is ok!
If only one row is inserted into the table then only the data of this row is in the table "inserted".

No, you're never too old to Yak'n'Roll if you're too young to die.

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-12 : 04:01:02

Webfred, I am not clear on this, we are using Select * from inserted means, i will get all the rows from table inserted.

quote:
Originally posted by webfred

No it is ok!
If only one row is inserted into the table then only the data of this row is in the table "inserted".

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-12 : 04:05:44

See the webfred's reply

insert DB2.dbo.TBL_Data
select * from inserted

it will work fine as your requirement

Vabhav T

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-12 : 04:14:38

Hi Vaibhav, I am too confused here. Can you give me the same syntax using my tables(Refer my starting post, where i gave my DB, tabel & column names).

quote:
Originally posted by vaibhavktiwari83

See the webfred's reply

insert DB2.dbo.TBL_Data
select * from inserted

it will work fine as your requirement

Vabhav T

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-12 : 04:18:09

It is exactly what you need.
If you insert a row into DB1.dbo.Tbl_Names then this row is at runtime of the trigger also in a "temp" triggertable named INSERTED.
So my given statement does exactly what you want.

No, you're never too old to Yak'n'Roll if you're too young to die.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-12 : 04:19:56

You just create this trigger in your DB1 database

CREATE TRIGGER trig_addrow
ON Tbl_Names
FOR INSERT
AS
BEGIN
insert DB2.dbo.TBL_Data
select * from inserted
END

Now after this at every insert in tbl_names will insert row in tbl_data table also...

Vabhav T

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-12 : 04:47:35

Thank you webfred & Viabhav for providing the solution.

One last doubt. "select * from inserted" Where is this table "Inserted"? On fly it will created when trigger fires and then it deleted when trigger's execution get completed?

quote:
Originally posted by vaibhavktiwari83

You just create this trigger in your DB1 database

CREATE TRIGGER trig_addrow
ON Tbl_Names
FOR INSERT
AS
BEGIN
insert DB2.dbo.TBL_Data
select * from inserted
END

Now after this at every insert in tbl_names will insert row in tbl_data table also...

Vabhav T

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-12 : 05:03:20

yes

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-03-12 : 05:24:28

How about replace the table on DB2 with a VIEW:


CREATE VIEW DB2.dbo.Tbl_Data 
AS
SELECT FName AS KName,
       LName AS GName,
       Addr AS Country
FROM DB1.dbo.Tbl_Names

The triggers discussed above are only to insert NEW rows into DB2. Your requirement is " If i update/insert/delete any data on Tbl_Names in Database DB1, automatically the effect should take place in Tbl_Data in Database DB2" which needs a more complicated trigger (still doable though).

"On fly it will created when trigger fires and then it deleted when trigger's execution get completed?"

Yes, that is exactly how it works

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-12 : 05:57:03

quote:
The triggers discussed above are only to insert NEW rows into DB2.

Sure - but you can see it was a good idea to reduce the complexity of creating triggers to one kind of trigger at a time.

quote:
How about replace the table on DB2 with a VIEW

I thought about that too but wasn't sure if a view can work from one db to another db.
If possible then it is the best way to solve all problems.

No, you're never too old to Yak'n'Roll if you're too young to die.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources