从表复制数据并插入不同的SQL Server数据库

I have two databases

我有两个数据库

• databaseone
• databasetwo

and I have a similar table in the database, name of the table is tableemployeedetails.

我在数据库中有一个类似的表,表的名称是tableemployeedetails。

In my databaseone, I have 500 columns in the table tableemployeedetails.

在我的databaseone中,表tablemployeedetails中有500列。

In my databasetwo, I have 10 columns in the table tableemployeedetails.

在我的数据库中,我在表tableemployeedetails中有10列。

I cannot use insert into select query because I want to insert the data into different database.

我不能使用insert into select query,因为我想将数据插入到不同的数据库中。

What is the best way to do this in my situation?

在我的情况下,这样做的最佳方法是什么?

I just want to merge tableemployeedetails in both the databases

我只想在两个数据库中合并tableemployeedetails

2 个解决方案

#1

Try this,

    insert into databasetwo..tableemployeedetails
    SELECT * FROM databaseone..tableemployeedetails  A
    WHERE NOT EXISTS (SELECT 1 FROM databasetwo..tableemployeedetails B
                      WHERE A.COLUMN=B.COLUMN
                      )

#2

If both databases has different records then you need two insert statements as below. If they have same then you need to prefer which database records are latest and then write an update in addition to the below insert.

如果两个数据库都有不同的记录,则需要两个插入语句,如下所示。如果它们相同,那么您需要更新哪些数据库记录是最新的,然后在下面的插入中添加更新。

insert into databasetwo..tableemployeedetails
SELECT * FROM databaseone..tableemployeedetails  d1
    left join databasetwo..tableemployeedetails d2 on A.PKKEY=B.PKKEY
    where d2.PKKEY is null

insert into databaseone..tableemployeedetails
SELECT * FROM databasetwo..tableemployeedetails  d2
    left join databaseone..tableemployeedetails d1 on A.PKKEY=B.PKKEY
    where d1.PKKEY is null