問(wèn)題描述

我使用的是 SQL Server 2008 Enterprise.我需要將服務(wù)器/實(shí)例Server Foo"、數(shù)據(jù)庫(kù)Foo"和表Foo"中的所有數(shù)據(jù)導(dǎo)入目標(biāo)服務(wù)器/實(shí)例Server Goo"、數(shù)據(jù)庫(kù)Goo"和表Goo".Table Foo 和 Table Goo 具有相同的架構(gòu).如果表 Goo 存在相同的行，我想保留 Goo 中的原始數(shù)據(jù)并忽略 Foo 中的導(dǎo)入行(表 Foo 和表 Goo 都有一個(gè)名為 CustomerID 的唯一標(biāo)識(shí)符類(lèi)型列，用作主鍵和聚集索引)，只是就像忽略重復(fù)鍵一樣.

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.

我正在尋找簡(jiǎn)單可靠的方法來(lái)編寫(xiě) T-SQL 來(lái)解決數(shù)據(jù)導(dǎo)出/導(dǎo)入問(wèn)題.有參考樣本嗎?

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

編輯 1:

我使用 MERGE 嘗試了以下解決方案，但遇到了來(lái)自 SQL Server Management Studio 的以下錯(cuò)誤.任何想法出了什么問(wèn)題?

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 => 服務(wù)器\實(shí)例名稱(chēng)；OrderDB => 數(shù)據(jù)庫(kù)名稱(chēng)；dbo => 模式名稱(chēng)；訂單 => 表名.

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
;

錯(cuò)誤信息:

消息 102，級(jí)別 15，狀態(tài) 1，第 16 行';' 附近的語(yǔ)法不正確.

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

提前致謝，喬治

推薦答案

在 SQL Server 2008 中，您可以在 SQL Server Mgmt studio 中編寫(xiě) Goo.Goo 表的腳本，并告訴它創(chuàng)建一個(gè)腳本來(lái)插入所有數(shù)據(jù)，使用T-SQL INSERT 語(yǔ)句.轉(zhuǎn)到對(duì)象資源管理器，右鍵單擊數(shù)據(jù)庫(kù)，選擇任務(wù) > 生成腳本"，選擇要為其生成數(shù)據(jù)插入語(yǔ)句的表，并確保在此處使用此選項(xiàng):

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:

然后可以在另一臺(tái)服務(wù)器上運(yùn)行它們以插入表格內(nèi)容.但是，在這種情況下，您必須自己處理插入可能的現(xiàn)有行.

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.

另一方面，如果兩臺(tái)服務(wù)器在同一網(wǎng)絡(luò)上，您可以使用鏈接服務(wù)器"功能并將源服務(wù)器鏈接到目標(biāo)服務(wù)器，然后使用 SQL Server 2008 MERGE 語(yǔ)句導(dǎo)入所有數(shù)據(jù)從源服務(wù)器的表到目標(biāo)服務(wù)器.

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.

在對(duì)象資源管理器中，轉(zhuǎn)到服務(wù)器對(duì)象"，然后鏈接服務(wù)器"，右鍵單擊并添加新鏈接服務(wù)器"以在兩個(gè)服務(wù)器之間建立連接:

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:

一旦服務(wù)器被鏈接，一個(gè)簡(jiǎn)單的 MERGE 語(yǔ)句(SQL Server 2008 中的新增功能)將允許您合并這兩個(gè)表中的數(shù)據(jù):

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
;

在此處閱讀有關(guān)新 MERGE 語(yǔ)句的更多信息:

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 聯(lián)機(jī)叢書(shū)中.

or in the SQL Server 2008 Books Online.

馬克

這篇關(guān)于將數(shù)據(jù)從一張表導(dǎo)入到另一張表的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！