将数据从一个数据库传输到另一个数据库

I have two databases, lets say Database A and B, with different datastructures. In Database A there is a table contacts. In Database B there is a table Accounts.

我有两个数据库,比如数据库A和B,具有不同的数据结构。在数据库A中有一个表联系人。在数据库B中有一个表帐户。

I want to transfer data from Database A table contacts to Database B table Accounts. I am use SQL Server 2005 and want to write sql scripts to do this.

我想将数据从数据库A表联系人传输到数据库B表帐户。我使用SQL Server 2005,并希望编写SQL脚本来执行此操作。

Can someone tell me whats the easiest and most efficient way to achieve this: a) If they are on the same SQL server b) If they are on different SQL servers.

有人可以告诉我最简单,最有效的方法:a)如果他们在同一个SQL服务器上b)如果他们在不同的SQL服务器上。

Cheers

3 个解决方案

#1

Use SSIS. It will work for both local and remote cases, and will enable you to set up a transform between the tables, to map columns to lother columns etc.

使用SSIS。它适用于本地和远程情况,并允许您在表之间设置转换,将列映射到其他列等。

#2

Is it a one off transfer? If it's a simple transfer I write a SELECT statement to create the INSERT statements, i.e.

这是一次转让吗?如果它是一个简单的传输我写一个SELECT语句来创建INSERT语句,即

SELECT 'INSERT INTO Accounts (ID, Name) VALUES (' + CAST(ID as VARCHAR) + ', ''' + Name  + ''')'
FROM Contacts

Run this on Database A - and it will spit out the all INSERT statements, which you copy and paste so you can run them on Database B.

在数据库A上运行它 - 它将吐出所有INSERT语句,您复制并粘贴这些语句,以便您可以在数据库B上运行它们。

Or, on the same database:

或者,在同一个数据库中:

INSERT INTO DatabaseA.dbo.Accounts (ID, Name) 
SELECT Id, Name
FROM DatabaseB.dbo.Contacts

Still not happy - try setting up linked servers: http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx

仍然不高兴 - 尝试设置链接服务器:http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx

#3

The easiest method isn't necessarily the most efficient. SSIS is likely the most efficient, as Mitch already indicated.

最简单的方法不一定是最有效的方法。正如米奇已经指出的那样,SSIS可能是最有效的。

The easiest (if you don't know SSIS already) is to just set up a linked server to the remote DB, and SELECT the data across using four-part naming. You set up a linked server using sp_addlinkedserver and sp_addlinkedsrvlogin (check BOL for the syntax), and query as follows:

最简单的(如果你还不知道SSIS)就是设置一个到远程数据库的链接服务器,并使用四部分命名来选择数据。使用sp_addlinkedserver和sp_addlinkedsrvlogin设置链接服务器(检查BOL的语法),并按如下方式查询:

INSERT INTO MyLocalTable (ColumnList)
SELECT <Columns> FROM RemoteServer.RemoteDB.RemoteSchema.RemoteTable

#1