更新多个表中的记录属性
我需要更新 [ISD_ID] 属性的不同表中的选择事件，我该如何完成此操作？
我可以使用以下语句获取拥有我要查找的记录的表集：
SELECT c1.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c1
INNER JOIN information_schema.COLUMNS c2
ON c1.TABLE_NAME = c2.TABLE_NAME
WHERE c1.COLUMN_NAME LIKE '%isd%id%' and c2.column_name LIKE '%schooldistrict%id%'
因此，现在我有一组我想要迭代的表名，并根据需要更新记录。
根据另一篇文章中的建议，我已经阅读了动态 SQL。所以我可以做类似 EXECUTE IMMEDIATE: ('update' + @tablename + ' set ISD_ID=37 where SchoolDistrictID=46') 的事情。
那么，我的问题是：如何迭代我的表名称并将它们作为 @tablename 变量替换到上面的语句中，以便我可以更新每个表中的值？
如果需要，我还可以使用 .NET 和 SSIS。
提前致谢;我对 SQL 不太精通，很高兴有机会学习。
I need to update select occurrences in different tables of the [ISD_ID] attribute, how might I accomplish this?
I can grab the set of tables that possess the records I'm looking for with this statement:
SELECT c1.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c1
INNER JOIN information_schema.COLUMNS c2
ON c1.TABLE_NAME = c2.TABLE_NAME
WHERE c1.COLUMN_NAME LIKE '%isd%id%' and c2.column_name LIKE '%schooldistrict%id%'
So now I have a set of table names that I'd like to iterate through, updating records as necessary.
Per a suggestion in another post, I've read up on Dynamic SQL. So I could do something like EXECUTE IMMEDIATE: ('update' + @tablename + ' set ISD_ID=37 where SchoolDistrictID=46') .
My question, then, is: how can I iterate through my table names and substitute them into the above statement as the @tablename variable, so that I can update the values in each table?
I also have the ability to use .NET and SSIS if need be.
Thanks in advance; I'm not so proficient with SQL and appreciate the opportunity to learn.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
奇怪的方法
您可以使用未记录的存储过程（请不要告诉 Remus Rusanu 我这么说），来执行 类似这样的。您可以使用 @where 条件来指定表必须包含给定的列。
更传统的方法是
老实说，当你完成“奇怪的方式”时，你最好还是用传统的方式来做，但我只是想彻底。
A weird way
You can use an undocumented stored procedure (please don't tell Remus Rusanu I said that), to do something like this. You'd use the @where condition to specify that the table has to contain a given column.
A more conventional way would be to
Honestly, by the time you are done with "weird way", you'd be better off doing it the conventional way, but I'm just trying to be thorough.
如果您想为此使用 SSIS，您可以将第一个查询的结果放入 RecordSet 目标中。
然后，您可以在保存记录集的变量上创建一个 foreach 循环 - 然后例如使用执行
"UPDATE " + (DT_STR,255,1252)@[User::tblname] 的表达式创建一个 SQL 任务+“SET .....”作为 sqlStatementSource。If you want to use SSIS for this you can take the result from your first query into a RecordSet destination.
You can then make a foreach loop on the variable that keeps the recordset - and then for example make a SQL Task with an expression that does your
"UPDATE " + (DT_STR,255,1252)@[User::tblname] + " SET ....."as sqlStatementSource.