C#从oracle中选取20000条记录需要插入到SQL2005中
我有一个 C# 控制台应用程序，它使用下面的代码从 Oracle DB 中提取 20 个字段，我想要一种有效的方法将它们插入到 SQL 2005 中
。显然，我不想在 while 循环中插入 20,000 个字段中的每一个。我正在考虑更改代码以使用数据集缓存所有记录，然后进行批量插入...
想法？
伪代码会很好，因为我是 Oracle 的新手。
这是我的代码，我正在测试与 Oracle 的连接并查看是否可以查看数据...现在我可以查看它，我想将其取出并进入 sql2005...我应该从这里做什么？
static void getData()
{
string connectionString = GetConnectionString();
using (OracleConnection connection = new OracleConnection())
{
connection.ConnectionString = connectionString;
connection.Open();
OracleCommand command = connection.CreateCommand();
string sql = "SELECT * FROM BUG";
command.CommandText = sql;
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
//string myField = (string)reader["Project"];
string myField = reader[0].ToString();
Console.WriteLine(myField);
}
}
}
i have a console app in c# that extracts 20 fields from an oracle DB witht he code below and i wanted an efficient way to insert them into SQL 2005.
i dotn want to insert each one of the 20,000 within the while loop, obviously. i was thinking to change the code to use a data set to cache all the records and then do a bulk insert...
thoughts?
pseudo code would be nice since i am new to oracle.
this is my code where i was testing getting a connection to oracle and seeing if i can view the data... now i can view it i want to get it out and into sql2005... what do i do from here?
static void getData()
{
string connectionString = GetConnectionString();
using (OracleConnection connection = new OracleConnection())
{
connection.ConnectionString = connectionString;
connection.Open();
OracleCommand command = connection.CreateCommand();
string sql = "SELECT * FROM BUG";
command.CommandText = sql;
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
//string myField = (string)reader["Project"];
string myField = reader[0].ToString();
Console.WriteLine(myField);
}
}
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以创建一个 CSV 文件，然后使用 BULK INSERT 将该文件插入到 SQL Server 中。查看此处示例。
You can create a CSV file and then use BULK INSERT to insert the file into SQL Server. Have a look here for an example.
带有缓存数据集的“批量”插入将与您不想编写的 while 循环完全相同！问题是，如果您尝试使用 Dataset 类的“批量”插入，您将失去对过程的控制。这最终是多余的工作。
也许最好的解决方案是使用 DataWriter，这样您就可以完全控制并且没有数据集开销。
The "bulk" insert with the cached Dataset will work exactly like the while loop you are not wanting to write! The problem is that you'll lose control of the process if you try to use the "bulk" insert of the Dataset class. It is extraneous work in the end.
Maybe the best solution is to use a DataWriter so that you have complete control and no Dataset overhead.
实际上，每个 sql 批次可以执行 100-1000 次插入。只需生成多个插入，然后提交即可。在第一个执行时预生成下一个 SELECT 批次。
You can actually do 100-1000 inserts per sql batch. Just generate multiple inserts, then submit. Pregenerate the next SELECT batch WHILE THE FIRST EXECUTES.