从数据库中获取行并将其插入另一个行

I have a table from which I read using SqlCommand variable, executing ExecuteReader on it an the on the returned SqlDataReader object I am interating to get each row, from where I do operations on the string to perform on insertion in the new DB using also a SqlCommand variable and executing ExecuteNonQuery().

我有一个表，我使用SqlCommand变量读取它，在它上面执行ExecuteReader和返回的SqlDataReader对象我正在集中获取每一行，我在那里对字符串执行操作，以便在新数据库中插入时也使用a SqlCommand变量并执行ExecuteNonQuery（）。

My question is if there is any more elegant/efficient way to do this ? Something like getting all the rows in one command and insert them all at once or anything else that is better than the current flow.

我的问题是，是否有更优雅/有效的方法来做到这一点？比如在一个命令中获取所有行并将它们全部插入或者比当前流程更好的任何其他内容。

UPDATE One important thing is that the tables are in different databases on different servers.

更新一个重要的事情是表位于不同服务器上的不同数据库中。

4 个解决方案

#1

I would suggest: Try it in one step.

我建议：一步到位。

INSERT INTO yourDataBase1.yourTable1 VALUES (Column1, Column2, ...)
SELECT Column1, Column2, ... FROM yourDataBase2.yourTable1

Benefits:

优点：

Less traffic over ethernet
通过以太网减少流量
Fast compared to other solutions (e.g. stored procedures)
与其他解决方案（例如存储过程）相比速度快

I would do this transaction-based in your C# application (Transmit / Rollback). So if something goes wrong it won't insert just half of your rows.

我会在你的C＃应用程序（传输/回滚）中执行此事务。因此，如果出现问题，它将不会插入一半的行。

#2

You could write a stored procedure or SqlCommand which looks something like

你可以编写一个类似的存储过程或SqlCommand

INSERT INTO DB1.dbo.TestTable(FirstName, LastName)
SELECT RemoteTable.FirstName, RemoteTable.LastName FROM
OPENROWSET('SQLOLEDB',
           'Server=x.x.x.x;Trusted_Connection=yes;user_id=xxxx;password=xxxx',
           'SELECT FirstName, LastName FROM DB2.dbo.TestTable' ) as RemoteTable

#3

I'm going to post a mix of two answers Jay Patel is the best method of doing this as you would like to use different servers and databases.

我将发布两个答案的混合Jay Patel是最好的方法，因为你想使用不同的服务器和数据库。

but wrapping it in a try catch and a transaction will be the best idea so i will take Jay Patel's Method and wrap it so there is some fail over for whatever reason it fails.

但是将它包装在try catch和一个事务中将是最好的主意，所以我将采用Jay Patel的方法并将其包装，以便因故失败而进行一些故障转移。

BEGIN TRAN -- START THE TRANSACTION BUT DONT PHSICALLY PUT THE ROWS IN THE DATABASE UNTIL COMMIT
BEGIN TRY
      INSERT INTO DB1.dbo.TestTable(FirstName, LastName)
    SELECT RemoteTable.FirstName, RemoteTable.LastName FROM
    OPENROWSET('SQLOLEDB',
               'Server=x.x.x.x;Trusted_Connection=yes;user_id=xxxx;password=xxxx',
               'SELECT FirstName, LastName FROM DB2.dbo.TestTable' ) as RemoteTable
END TRY
BEGIN CATCH
ROLLBACK -- IF THE INSERT FAILS DONT INSERT ANYROWS INTO THE DATABASE
END CATCH

COMMIT --USED FOR COMMITING THE ROWS TO THE DATABASE THEY ARE NOT ACTUALLY IN THE DATABASE UNTIL THIS HAS BEEN COMMITED

#4

If the manipulation is something you need C# .NET for then you have two options

如果操作是你需要C＃.NET的东西那么你有两个选择

1) build up and insert multiple rows using the syntax:
values (), ()
you can insert more than one row in a single statement - this will make it less chatty
best to keep the total values less than 1000 for each insert

1）使用以下语法构建和插入多行：values（），（）您可以在单个语句中插入多行 - 这样可以减少每个插入的总值小于1000的问题

2) use a TVP (Table Value Parameter)

2）使用TVP（表值参数）

In you can perform the manipulation in TSQL then just an insert.
Or use CLR so you can use you C# in the insert.

在你可以在TSQL中执行操作然后只是插入。或者使用CLR，这样你就可以在插入中使用C＃。

Via Linked Server you can traverse servers.
You just use a 4 part name in the insert / select.

通过Linked Server，您可以遍历服务器。您只需在插入/选择中使用4部分名称。

#1