将特定数据从一个数据库移动到另一个数

This is for SQL Server. I have tables Product (product_id pk) and Customers (cust_id pk). And a few other tables that have the above as foreign key.

这适用于SQL Server。我有表Product（product_id pk）和Customers（cust_id pk）。以及其他一些具有上述外键的表。

I need to come up with a good set of INSERT statements that can move rows from the tables above, for a specific Product from one database to another. Is there a good tool that can do this?

我需要提出一组好的INSERT语句，这些语句可以将上表中的行从一个数据库移动到另一个数据库。有没有一个好的工具可以做到这一点？

The twist is also that the different databases have different ids for products and customers - so inserts should first look up the ids based on something else like product name and customer name (assuming there are no duplicates).

另一个问题是，不同的数据库对产品和客户有不同的ID - 因此插入应首先根据产品名称和客户名称（假设没有重复项）来查找ID。

1 个解决方案

#1

If the databases are within the same server, you may use the following assuming they have the same table structure

如果数据库位于同一服务器中，则可以使用以下假设它们具有相同的表结构

USE [TESTDB]

SELECT *
  INTO #values
  FROM producttbl

USE [OTHERDB]

INSERT INTO tbl_product
SELECT *
  FROM #values

The twist is also that the different databases have different ids for products and customers - so inserts should first look up the ids based on something else like product name and customer name (assuming there are no duplicates).

另一个问题是，不同的数据库对产品和客户有不同的ID - 因此插入应首先根据产品名称和客户名称（假设没有重复项）来查找ID。

For this, you have to create an SQL statement with conditions to lookup for those specific records

为此，您必须创建一个SQL语句，其条件是查找这些特定记录

#1