在SQL过程中使用循环更新数据库表列 [英] Update database table column using loop in SQL procedure
问题描述
我有excel包含专利号和案例号。我希望在每个专利记录中输入数据库表中的案例编号。为此,我首先阅读execl并将其传输到datatable中,现在在datatable上使用foreach循环,我使用下面的命令运行producer来更新表
更新table1 set caseno = @ caseno其中patent = @ patentno。参数@caseno和@patentno包含从excel转移到datatable的值。
这将更新我的表,但它将命中数据库的时间。我需要使用循环来执行此SQL过程。
请帮助新的SQL
我尝试过:
数据表DT = excel数据;
Foreach(dt.table中的DT行[0]
(
更新table1 set caseno = @ caseno where patent = @ patentno。
)
试试这样使用c#但是它命中数据库来计算excel中的行数这个坏。我想在SQL程序中使用相同。我将数据表作为参数发送到sql但我循环它并更新基于专利号。请帮助。
I have excel contains patent no and case no. I want to make entry of case number in database table with respect every patent record. For this first I read execl and transfer it into datatable,Now using foreach loop on datatable , I m run producer to update table using command below
Update table1 set caseno=@caseno where patent =@patentno. Parameter @caseno and @patentno contains value from excel which transfer to datatable.
This will update my table but it will hit database numbers of time. I need to do this SQL procedure using loop.
Please help new to SQL
What I have tried:
Datatable DT =excel data;
Foreach (DT rows in dt.table[0]
(
Update table1 set caseno=@caseno where patent =@patentno.
)
Try like this using c# but it hit database to count of row in excel which bad .I want to same in SQL procedure. I will send datatable as parameter to sql but I loop it and update base on patent number.please help .
推荐答案
如果我理解正确的要求,一个解决方案是:
- 从Excel读取数据到数据表,例如使用OleDbConnection
- 将数据表传递给程序
- 更新/插入所需的行
您可能找到的链接很少有用:
- 将Excel工作表读入DataTable,通用方法 [ ^ ]
- 如何将多个记录传递给存储过程 [ ^ ]
程序中的更新是什么,如果不需要,请不要使用游标。如果这是更新现有行的简单情况,您可以使用UPDATE FROM语法,如 UPDATE(Transact-SQL)中所述) [ ^ ]。
如果您需要根据行的存在进行更新或插入,请考虑使用 MERGE(Transact-SQL) [ ^ ]
If I understand the requirement correctly, one solution would be:
- read the data from Excel to a data table, for example using OleDbConnection
- pass the data table to a procedure
- update/insert the desired rows
Few links that you may find useful:
- Reading an Excel Sheet into a DataTable, generic method[^]
- How to pass multiple records to a Stored Procedure[^]
What comes to the update in the procedure, don't use cursors if you don't need to. If this is a simple case of updating existing rows you can use UPDATE FROM syntax as described in UPDATE (Transact-SQL)[^].
If you need to both update or insert depending on the existence of the row, consider using MERGE (Transact-SQL)[^]
这篇关于在SQL过程中使用循环更新数据库表列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!