在 SSIS 2008 中使用联接执行参数化删除的语法是什么？
我正在尝试使用 OLE DB 命令使用输入文件每一行中的数据执行删除。在 sql server 中手动运行时，实际查询工作正常（给定 tableB.otherID 与 int 进行比较），但我在参数化它时遇到问题。
delete tableA from tableA
where tableA.ID = ?
上面的查询运行，并允许我将输入列之一分配给 tableA.ID。这是我所期望的。
但是尝试
delete tableA from tableA
INNER JOIN tableB ON tableB.ID = tableA.ID
where tableB.OtherID = ?
会抛出错误（“无法绑定多部分标识符 tableB.OtherID”）。硬编码一个值来代替“？”阻止出现此错误。
看起来这是正确的语法，上面有什么问题吗？
I'm trying to use an OLE DB Command to perform a delete using data from each row of my input file. The actual query works fine when running manually in sql server (given tableB.otherID is compared to an int), but I'm having issues parameterising it.
delete tableA from tableA
where tableA.ID = ?
The above query runs, and allows me to assign one of my input columns to tableA.ID. This is what I would expect.
Trying
delete tableA from tableA
INNER JOIN tableB ON tableB.ID = tableA.ID
where tableB.OtherID = ?
Throws up an error however ("The multi-part identifier tableB.OtherID could not be bound"). Hardcoding a value in place of the '?' stops this error from appearing.
It seems like this would be the correct syntax, is there anything wrong with the above?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这似乎是 SSIS 的一个错误/限制，我发现自己无法使用连接执行类似的参数化更新语句。
我最终使用的解决方案是使用我想要的删除语句创建一个临时存储过程，然后直接将参数传递给它。
This seems to be a bug/limitation with SSIS, I've found myself unable to perform similar parameterised update statements using a join.
Solution I ended up using was creating a temporary stored procedure with the delete statement I wanted, and passing the parameter to it directly.
我认为你想要的 TSQL 语法是：
I think the TSQL syntax you want is:
其中 @OrderID 应该是 SSIS 中的变量。
Where @OrderID should be your variable in SSIS.
根据需要使用执行 Sql 任务删除的行数，速度会变得相当慢。
如果发生这种情况，对我有用的解决方案是将需要删除的行的键放入临时表中，然后当它们都在其中时，发出一个语句，删除单个语句中的所有这些行并清除临时表桌子。这样速度更快，额外的好处是您不必使用古怪的 ?句法。我从来不喜欢这样，当 sql 变得稍微复杂一点时，太容易混淆了。
问候格特-扬
Depending on how many rows you need to delete using an Execute Sql task becomes slow quite quickly.
If that happens the solution that worked for me is putting the keys of the rows that need to be deleteed into a staging table, then when they're all in there issue one statement that deletes all those rows in a single statement and purges the staging table. Much quicker that way, added beneift is that you don;t have to use the quirky ? syntax. I never liked that, much too easy to mix stuff up when the sql becomes a little more complicated.
Regards Gert-Jan