使用DAAB将多行插入数据库中，而无需多次往返 [英] Insert multiple rows to a DB using DAAB without multiple round trips
问题描述
大家好，
我正在尝试在数据库中插入多行.我希望能够遍历所有记录并一次插入一条记录，而不是希望效率更高并且可以批量插入(即，一条语句一次性发送所有数据).你知道有没有办法做到这一点?
仅供参考:我希望使用EntLib(MS Enterprise Library 4.1)提供的DAAB(数据访问应用程序块).我的目标数据库是Sybase ASE 12.5，尽管我的源数据库是SQL Server 2008，但我无法利用SQLBulkCopy命令.理想情况下，我希望该解决方案不是特定于数据库的，以防我们需要将该解决方案重新用于与其他系统通信''.
到目前为止，包含示例/PoC代码.在这里，我假设源系统具有存储过程，该存储过程以与目标系统中要上传到的表中的格式相同的格式返回数据(即列名，类型和顺序相同) .
Hi Guys,
I''m trying to insert multiple rows into a database. Rather than looping through all records and inserting them one at a time, I''m hoping to be more efficient and do a bulk insert (i.e. one statement to send all data in one go). Do you know if there''s a way to do this?
FYI: I''m hoping to use the DAAB (Data Access Application Block) provided by EntLib (MS Enterprise Library 4.1). I can''t take advantage of the SQLBulkCopy command as my destination db is Sybase ASE 12.5, though my source is SQL Server 2008. Ideally I''d like the solution to be non DB specific in case we need to reuse this solution for communication with other systems'' dbs.
Example / PoC code so far is included below. Here I''m assuming that the source system has a stored procedure which returns the data in the same format as it appears in the table to which it will be uploaded in the destination system (i.e. column names, types and order are the same).
class Program
{
const CommandType COMMAND_TYPE = CommandType.StoredProcedure;
const string COMMAND_TEXT = "MyStoredProc"; //pulls back data in the format expected by the desintaiton table
const string SOURCE_TABLE = "Table"; //tables returned by stored procs are always called Table
const string DEST_TABLE = "Customers"; //generic example table name
const string SOURCE_DB = "Source";
const string DEST_DB = "Destination";
static void Main(string[] args)
{
Database source = DatabaseFactory.CreateDatabase(SOURCE_DB);
Database destination = DatabaseFactory.CreateDatabase(DEST_DB);
using (DataSet ds = source.ExecuteDataSet(COMMAND_TYPE, COMMAND_TEXT))
{
//begin made up code to illustrate
destination.UploadLoadDataSet(DEST_TABLE, ds, SOURCE_TABLE);
//end made up code to illustrate
}
}
}
我希望尽量减少拨打电话的原因是，该应用程序将在英国运行，从此处的数据库中提取数据，但通过WAN将其发送到法国的Sybase数据库.将会有几百万行数据.对DB的每个单独调用都会增加另一个往返，因此将严重影响性能.我的备份计划是使用脚本执行BCP导出和导入到每个方面，但是这使得捕获异常更加困难，并且似乎有些怪异.
预先感谢您的帮助，
JB
The reason I''m hoping to minimise the number of calls being made is this application will be running in the UK, pulling data from a DB here, but sending it over the WAN to a Sybase DB in France. There will be a few million rows of data. Each individual call to the DB would add another round trip, so will severly impact performance. My backup plan is to use scripts to perform BCP exports & imports on each side, but that makes catching exceptions a little harder and seems a little hacky.
Thanks in advance for your help,
JB
推荐答案
sp_executesql怎么样(如果在sybase中可用)?您可以将所有插入语句合并为一个用``;''分隔的语句，然后调用sp_executesql或sybase中类似的语句.我想每个数据库都应该与此类似.
另外，我不确定这是否是您所需的最佳方法.无论如何，我的2美分！
What about sp_executesql (if available in sybase)? You could combine all the insert statements as a single statement separated by '';'' and call sp_executesql or something similar in sybase. I guess every database should have something similar to this.
Also, I am not sure if this is the best way to do what you need. Anyways, my 2 cents!
是否可以使用FTP或TCP/IP简单地将表作为二进制文件传输，然后让远程计算机自己完成所有数据库的工作?
如果要确保某种恢复系统(在传输中断的情况下)，则可以发送较小的文件，并且至少可以在已知点重新开始传输.
Would it be possible to simply transfer the table as a binary file using FTP or TCP/IP, and then let the remote machine do all the database work itself?
If you want to ensure some kind of recovery system (in the event the transfer is interrupted), you could send smaller files and alt least beable to restart the transfer at a known point.
如果您只能使用SQL Server到SQL Server，可以使用SQL Server复制
If you were to use only SQL Server to SQL Server, you can use SQL Server Replication
这篇关于使用DAAB将多行插入数据库中，而无需多次往返的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
