問題描述
我使用的是 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.
馬克
這篇關于將數據從一張表導入到另一張表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！