SQLTeam.com | Weblogs | Forums

Copy data between tow database


#1

Hi Friends
I've got two data base including the same table
DB1.Table1
DB2.Table2
I need Transfer data from DB1.Table1 To DB2.Table2
when Insert data in DB1.Table1 Online


#2

ON INSERT Trigger? (maybe you also want UPDATEs and DELETEs too? What if link between DB1 and DB2 goes down?

Replication?

The table cannot be updated on DB2, right?

Both tables on the same server? Perhaps just use a SYNONYM on DB2 so that the DB1 table is used instead of having a (duplicate) table on DB2. I guess this would also work for a remote server, but would be fragile if the COMMs go down.


#3

thank you Kristen

both DBs on same server
and columns in Table 1 same as columns in table2.
if i want to create SYNONYM for Table1 from DB1 to DB2 can i make the SYNONYM name different from table 1 original name


#4

can create on insert trigger on the created SYNONYM


#5

Sure, and/or you can use a different Schema (although fairly sure that Scott maybe?? told me that was a bad idea (from efficiency PoV)

But I think:

If you currently have DB1.SomeTableName and DB2.SomeTableName, with identical column names, then what you want to do is DROP DB2.SomeTableName and create a SYNONYM on DB2, called SomeTableName, "pointing" to the Table/Columns on DB1?