使用 Invoke-SQLCmd 并返回删除/影响的行数 [英] Use Invoke-SQLCmd and return number of rows deleted/affected

问题描述

我有一个非常简单的 powershell 命令，其中 $query 只是从 mytable 中删除"

I have a very simple powershell command, where $query is just "delete from mytable"

Invoke-sqlcmd -ServerInstance $databaseserver -Username $databaseuser -Password $databasepassword -Database $databasename -Query $query| Out-File -filePath $output

但是，这不会输出受影响的行数?

However, this does NOT output the number of rows affected?

SQLCMD 中的等效项确实输出不受影响的行:

The equivalent in SQLCMD does output the no rows affected:

& sqlcmd -S . -U $databaseuser -P $databasepassword -Q $query    -o $output

有没有办法让Invoke-sqlcmd返回行数?

Is there any way to get Invoke-sqlcmd to return the number of rows?

推荐答案

您可以在查询后立即调用 SQL 中的 ROWCOUNT 函数功能来执行以下操作:

You could call the ROWCOUNT function feature in SQL immediately after your query to do the following:

Select @@ROWCOUNT

以您的 PowerShell 为例:

Using your PowerShell as an example:

$query = "DELETE FROM MyTable; SELECT @@ROWCOUNT AS DeletedRows"
$Results = Invoke-sqlcmd -ServerInstance $databaseserver -Username $databaseuser -Password $databasepassword -Database $databasename -Query $query| Out-File -filePath $output
Write-Host "Total rows deleted: $($Results.DeletedRows)"

有关 ROWCOUNT 的更多信息@http:///technet.microsoft.com/en-us/library/ms187316(v=sql.90).aspx

More information on ROWCOUNT @ http://technet.microsoft.com/en-us/library/ms187316(v=sql.90).aspx

此外，您可以在删除记录之前进行行计数，并在删除后进行另一行计数，然后进行差异化.这仅适用于插入/删除的记录，不适用于更新的记录.

In addition, you could take a row count before the deletion of records and do another row count after deletion then do the difference. This will only work on inserted/deleted records and will not work on for records that are updated.

SELECT COUNT( my_table.my_col ) AS row_count

这篇关于使用 Invoke-SQLCmd 并返回删除/影响的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！