在 SQL 中遍历多个 CSV
我有一个 SQL Server 2008 数据库。该数据库有一个存储过程，可以更新多条记录。这些记录的 ID 存储在通过逗号分隔字符串传入的参数中。与每个 id 关联的属性值通过另外两个逗号分隔的字符串传入。假设长度（以标记为单位）和值的顺序是正确的。例如，这三个字符串可能如下所示：
Param1='1,2,3,4,5'
Param2='Bill,Jill,Phil,Will,Jack'
Param3='Smith,Li,Wong,Jos,Dee'
我的挑战是，我不确定实际解析这三个 CSV 并更新相应记录的最佳方法是什么。我可以访问名为 ConvertCSVtoTable 的过程，它将 CSV 转换为临时记录表。所以Param1会
1
2
3
4
5
在调用过程后返回。我想过一个光标，但后来它似乎变得非常混乱。
有人可以告诉我/告诉我，解决这个问题的最佳方法是什么？
I have a SQL Server 2008 database. This database has a stored procedure that will update several records. The ids of these records are stored in a parameter that is passed in via a comma-delimited string. The property values associated with each of these ids are passed in via two other comma-delimited strings. It is assumed that the length (in terms of tokens) and the orders of the values are correct. For instance, the three strings may look like this:
Param1='1,2,3,4,5'
Param2='Bill,Jill,Phil,Will,Jack'
Param3='Smith,Li,Wong,Jos,Dee'
My challenge is, I'm not sure what the best way to actually go about parsing these three CSVs and updating the corresponding records are. I have access to a procedure named ConvertCSVtoTable, which converts a CSV to a temp table of records. So Param1 would return
1
2
3
4
5
after the procedure was called. I thought about a cursor, but then it seems to get really messy.
Can someone tell me/show me, what the best way to address this problem is?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我会考虑重新处理您的程序的输入。由于您运行的是 SQL 2008，我的第一选择是使用 表-值参数。我的第二个选择是将参数作为 XML 传递。正如您所知，您当前的方法确实令人头疼并且更容易出错。
I'd give some thought to reworking the inputs to your procedure. Since you're running SQL 2008, my first choice would be to use a table-valued parameter. My second choice would be to pass the parameters as XML. Your current method, as you already know, is a real headache and is more error prone.
您可以使用批量加载将值插入到 tmp 表中，然后对它们进行 PIVOT 并插入到正确的表中。
You can use bulk load to insert values to tmp table after that PIVOT them and insert to proper one.