Syncronizing Two Database Tables

[GDX]

Hello I have a table in 2 databases on Named Data1.Tbl1 and Data2.Tbl1

They both need to keep the information up to date, Data1.Tbl1 is the main table but if someone adds data in Data2.Tbl1 I need it to update properly in Data1

Can someone please tell me how do go about doing this? The 2 databases are on the same physical box if that helps. And they both have SQL IDENTITY on a column called "ID"

Gordon R. Durgha
gd@vslink.net

http://www.vslink.net

http://www.brainbench.com/transcript.jsp?pid=1217371

 

[SQLSister]

Do these need to be syched in real-time or only periodically? At any rate you should look at replication or triggers. Depending on your needs one of these will be the answer.

 

[ESquared]

If you have an identity column in each table then you're going to have a problem because they can have different records under the same ID.

 

[GDX]

This is true but initially they will be the same because in Data2 will be an exact copy of Data1 and i was thinking about putting triggers in both databases to sync with each other for update and inserts what do you think?

Gordon R. Durgha
gd@vslink.net

http://www.vslink.net

http://www.brainbench.com/transcript.jsp?pid=1217371

 

[ESquared]

If it's on the same server why do you need two tables? I can't think of any conceivable reason you'd need two separate tables since you are looking to make them function as one.

 

[nigelrivett]

If both tables are being inserted into then the identities will get out of synch.

Simplest method is to hold the latest id transferred then run an sp

declare @id int
select @id = lastid from idtbl

select top 1000 * into #a from Data2.Tbl1 where id > @id order by @id

begin tran

update idtbl set lastid = select max(id) from #a)
if @@error <> 0
begin
rollback tran
raiserror('failed',16,-1)
return
end

insert Tbl1 (cols)
select (cols)
from #a

if @@error <> 0
begin
rollback tran
raiserror('failed',16,-1)
return
end

commit tran

And do the same on the other table.
Schedule every minute (in a loop until no data to transfer) and it will keep things fairly up to date with little impact on the two databases.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[ESquared]

Hey GDX, are you going to be letting users make changes to already-existing rows?

If so, you have a big nightmare and you should forget about triggers and such to synchronize them when they're on the same physical box.

Just pick one database/server instance in which to create a view (with the same name as the table) that returns the table from the other database/server instance.

[tt]SELECT * FROM Database.Owner.Table[/tt]

 

[GDX]

Wow thats a great idea ESquared i will propose that tomorrow to the managers can't believe i didnt think about that!!

Gordon R. Durgha
gd@vslink.net

http://www.vslink.net

http://www.brainbench.com/transcript.jsp?pid=1217371