如何使用 INSERT SELECT (T-SQL) 检查是否插入了任何内容

发布于 2024-09-09 09:59:36 字数 410 浏览 5 评论 0原文

我有一个插入语句：

insert into parentTbl
select firstId, secondId, thirdId, dateTm
from importTbl
where codeId = @codeIdParam

我需要可靠地查明该插入是否插入了任何内容。理想情况下，我想将 @insertedCount 变量设置为插入的行数，即使是 0。

我当前正在使用：

set @insertedCount = @@ROWCOUNT

但这似乎只能获取最后插入的行数 -问题是，如果 INSERT SELECT 语句未插入任何内容，@@ROWCOUNT 不会返回 0。

原文

I have an insert statement:

insert into parentTbl
select firstId, secondId, thirdId, dateTm
from importTbl
where codeId = @codeIdParam

I need to reliably find out if that insert inserted anything at all. Ideally, I would like to set a @insertedCount variable to the number of rows inserted, even if that is 0.

I am currently using:

set @insertedCount = @@ROWCOUNT

But that only seems to get the last number of inserted rows - the problem is that if the INSERT SELECT statement did not insert anything the @@ROWCOUNT does not return 0.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

青春如此纠结 2024-09-16 09:59:36

您可以尝试使用 OUTPUT 子句，这将为每个插入的行返回一行；类似于：

insert into parentTbl
output inserted.firstId
select firstId, secondId, thirdId, dateTm
from importTbl
where codeId = @codeIdParam

这将为您提供一个结果集，其中包含每个插入行的 firstId，然后您可以对其进行计数。
一种方法是输出到 table-var，然后在最后执行select count(*) from @tableVar 来获取插入计数。

You could try using an OUTPUT clause, which would return one row per inserted row; something like:

insert into parentTbl
output inserted.firstId
select firstId, secondId, thirdId, dateTm
from importTbl
where codeId = @codeIdParam

This would give you a resultset with the firstIds of every inserted row, which you could then run a count on.
One way would be to output into a table-var, and then do a select count(*) from @tableVar at the end to get your insert-count.

回复收藏 0 原文

~没有更多了~