将数据从一张表导入到另一张表

问题描述

我使用的是 SQL Server 2008 Enterprise.我需要将服务器/实例Server Foo"、数据库Foo"和表Foo"中的所有数据导入目标服务器/实例Server Goo"、数据库Goo"和表Goo".Table Foo 和 Table Goo 具有相同的架构.如果表 Goo 存在相同的行，我想保留 Goo 中的原始数据并忽略 Foo 中的导入行(表 Foo 和表 Goo 都有一个名为 CustomerID 的唯一标识符类型列，用作主键和聚集索引)，只是就像忽略重复键一样.

I am using SQL Server 2008 Enterprise. I need to import all data from Server/Instance "Server Foo", Database "Foo" and table "Foo", into destination Server/Instance "Server Goo", Database "Goo" and table "Goo". Table Foo and Table Goo are of the same schema. If the same row exists for table Goo, I want to keep the origin data in Goo and ingore the import row in Foo (table Foo and table Goo both has a uniqueidentifier type column called CustomerID which acts as primary key and clustered index), just like ignore duplicate key does.

我正在寻找简单可靠的方法来编写 T-SQL 来解决数据导出/导入问题.有参考样本吗?

I am looking for simple and reliable ways to write T-SQL to solve data export/import issue. Any reference samples?

编辑 1:

我使用 MERGE 尝试了以下解决方案，但遇到了来自 SQL Server Management Studio 的以下错误.任何想法出了什么问题?

I have tried the below solution using MERGE, but met with the following error from SQL Server Management Studio. Any ideas what is wrong?

更多信息:

LabTest1\SQLServer2008 => 服务器\实例名称；OrderDB => 数据库名称；dbo => 模式名称；订单 => 表名.

LabTest1\SQLServer2008 => Server\Instance name; OrderDB => DB name; dbo => schema name; Orders => Table name.

merge into [dbo].[Orders] as Target
using "LabTest1\SQLServer2008.OrderDB.dbo.Orders" as source
on target.Hash = source.Hash
when not matched then
INSERT     ([Hash]
           ,[Order]
           ,[Name]
           ,[CreationTime]
           ,[Description])
     VALUES
     (
     source.[Hash], source.[Order], source.[Name], source.[CreationTime], source.[Description]
     )
when MATCHED then
;

错误信息:

消息 102，级别 15，状态 1，第 16 行';' 附近的语法不正确.

Msg 102, Level 15, State 1, Line 16 Incorrect syntax near ';'.

提前致谢，乔治

推荐答案

在 SQL Server 2008 中，您可以在 SQL Server Mgmt studio 中编写 Goo.Goo 表的脚本，并告诉它创建一个脚本来插入所有数据，使用T-SQL INSERT 语句.转到对象资源管理器，右键单击数据库，选择任务 > 生成脚本"，选择要为其生成数据插入语句的表，并确保在此处使用此选项:

In SQL Server 2008, you could script out your Goo.Goo table in SQL Server Mgmt studio and tell it to also create a script to insert all data by using T-SQL INSERT statements. Go the the Object Explorer, right-click on the database, pick "Tasks > Generate Scripts", pick the table you want to generate the data insert statements for, and make sure to use this option here:

然后可以在另一台服务器上运行它们以插入表格内容.但是，在这种情况下，您必须自己处理插入可能的现有行.

Those could then be run on the other server to insert the table contents. In this case, however, you'd have to handle inserting possible existing rows yourself.

另一方面，如果两台服务器在同一网络上，您可以使用链接服务器"功能并将源服务器链接到目标服务器，然后使用 SQL Server 2008 MERGE 语句导入所有数据从源服务器的表到目标服务器.

On the other hand, if both servers are on the same network, you could just use the "Linked Server" feature and link the source server to the target server and then use the SQL Server 2008 MERGE statement to import all the data from the source srever's table into the target server.

在对象资源管理器中，转到服务器对象"，然后链接服务器"，右键单击并添加新链接服务器"以在两个服务器之间建立连接:

In the Object Explorer, go to "Server Objects", then "Linked Servers", right-click and "Add new linked server" to establish a connection between the two servers:

一旦服务器被链接，一个简单的 MERGE 语句(SQL Server 2008 中的新增功能)将允许您合并这两个表中的数据:

Once the servers are linked, a simple MERGE statement (new in SQL Server 2008) will allow you to merge the data from those two tables:

MERGE 
  INTO Goo.Goo as Target
  USING Foo.Foo.dbo.Foo as Source
  ON Source.ID = Target.ID
WHEN NOT MATCHED THEN
  INSERT (field1, field2, field3)
  VALUES (source.field1, source.field2, source.field3)  
WHEN MATCHED THEN
  -- do nothing
;

在此处阅读有关新 MERGE 语句的更多信息:

Read more about the new MERGE statement here:

• http://www.builderau.com.au/program/sqlserver/soa/Using-SQL-Server-2008-s-MERGE-statement/0,339028455,339283059,00.htm
• http://www.sqlservercentral.com/articles/Advanced+Querying/3122/

或在 SQL Server 2008 联机丛书中.

or in the SQL Server 2008 Books Online.

马克

这篇关于将数据从一张表导入到另一张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！