IN SQL DATA TRANSFER Between To different databases

Question

For example, in Test12 DB I have two tables

Table 1: Emp_Details:

Emp_ID int
[Emp_Name] [varchar](50) NULL,

Table 2: salary:

[Sal_ID] [int] IDENTITY(1,1) NOT NULL,
[Salary] [varchar](50) NULL,

I want to update and insert automatically (when they are modified) Emp_Name from Emp_Details table and Salary from Salary table

To another Database, Test22, which has a table DepTable:

Dep_ID [int] 
Dep_Name  varchar(50) NULL,
Emp_Name varchar(50) NULL,  
Salary1  varchar(50) NULL,

Respectively, if you have any ideas, please help me.

Are you asking for some kind of synchronization/replication? — jarlh, Feb 17 '15 at 14:43
You can write triggers, you can use change data capture, etc. Please write some more information and please tell why you want to do that - i am pretty sure that you should do that in the way you want right now — sdrzymala, Feb 17 '15 at 15:15
I am not sure why you want to do this as that structure is breaking normalization. That means it will be difficult to properly maintain over time, so it is best not to use that structure if you have any choice about it. If you have control over this design, fix it now — HLGEM, Feb 11 '16 at 22:26

score 0 · Answer 1 · edited May 23 '17 at 11:59

You can refer to a table in another database using a three-part identifier: <db>.<schema>.<table>. So you can specify:

SELECT * FROM Test12.dbo.Emp_Details

or:

INSERT INTO Test22.dbo.Dep_ID ...

If they're on different servers or in different instances of the same server, you should look into a Linked server which lets you use a four-part identifier: <linked_server_name>.<db>.<schema>.<table>.

To do it automatically, you could use a trigger or a task with SQL Agent that runs periodically, or an SSIS package.

IN SQL DATA TRANSFER Between To different databases

1 Answers1