Connect two tables from different databases on same server

Question

Is it possible to connect two tables from different databases on the same SQL Server 2008 R2 instance? When one is being updated or inserted into, the other table should be updated automatically.

Example:

Table 1 from Customer database
Table 2 from Supplier database

When something is inserted into Table 1, at the same time Table 2 should be updated with same data of Table 1.

Please check this [link](http://stackoverflow.com/questions/6652286/sql-server-trigger-between-2-databases) to trigger for two database objects — Siva G, Jan 19 '16 at 05:56

TT. · Answer 1 · 2019-07-01T09:55:59.913

One way is to create a SYNONYM for Customer.dbo.Table1 in the Supplier database. You can then query the synonym as though it were in the Supplier database.

Suppose you have the Customer database with Table1 and the Supplier database with Table2. Suppose both tables are in the dbo schema. You can then test the following in SQL Server Management Studio (SSMS):

USE Supplier; -- Tell SSMS to switch to the Supplier database

CREATE SYNONYM Table1 FOR Customer.dbo.Table1; -- this creates a synonym permanently in the Supplier database; you only need to execute this once

SELECT * FROM Table1; -- select all from Customer.dbo.Table1, from within the context of database Supplier

This way there is no need for TRIGGERs and the corresponding overhead (you would need 3 TRIGGERs to keep the tables in sync).

Ruslan K. · Answer 2 · 2016-01-19T08:04:46.960

0

USE Customer
GO

CREATE TRIGGER dbo.Table1_AI
   ON dbo.Table1
   AFTER INSERT
AS 
BEGIN
    IF (@@ROWCOUNT = 0)
        RETURN;

    SET NOCOUNT ON;

    UPDATE t2
        SET ColumnX = i.ColumnX, ColumnY = i.ColumnY
        FROM Supplier.dbo.Table2 t2
        INNER JOIN inserted i on i.id = t2.id;
END
GO

Replace ColumnX = i.ColumnX, ColumnY = i.ColumnY and i.id = t2.id considering your logic.

edited Jan 19 '16 at 08:04

answered Jan 19 '16 at 07:33

Ruslan K.

1,912
1
15
18

Now,question is what is UPDATE t2 – Md. Didarul Islam Jan 19 '16 at 07:48
t2 is alias for table Supplier.dbo.Table2 – Ruslan K. Jan 19 '16 at 07:52
sorry this code will not wok on different database.this is for just on same database. – Md. Didarul Islam Jan 19 '16 at 07:52
i want to insert one table data into an other table.by using trigger. – Md. Didarul Islam Jan 19 '16 at 07:53
sorry, i want to insert one table into another on different database. – Md. Didarul Islam Jan 19 '16 at 07:55
@Md.DidarulIslam Have you at least tried it? You say *will* not work as though you know but haven't tried. Did you at least try? – TT. Jan 19 '16 at 07:56
like one database name is Biolite an other is HRM in same server .now i want to insert table data from biolite to HRM. – Md. Didarul Islam Jan 19 '16 at 07:58
@RuslanK. This is one of the triggers that would be needed. Triggers for UPDATE/DELETE would also be needed to fully link both tables. Also, there is very little context you provide in the answer. Some minimal context would be good here. – TT. Jan 19 '16 at 07:59
@TT. you are right, but question contains too little information – Ruslan K. Jan 19 '16 at 08:03
@Ruslank forget everything,now what i want is trigger for insert data from one to another table . Ex: Database name is Biolite and table name is Event_Log an other one is HRM table name is From_Device now help me to do. Select from Event_Log and INSERT INTO From_Device – Md. Didarul Islam Jan 19 '16 at 08:14

Connect two tables from different databases on same server

2 Answers2