如何在 Powershell 中使用 Read-SqlTableData 在 SQL 中选择最后的 X 条记录

Question

所以我正在编写一个Powershell脚本，并利用模块"sqlserver"来使用"Read-SqlTableData"cmdlet。我看到有一个 -topN X 过滤器，它从数据表的顶部选择，但我们表的设置方式，最旧的记录在顶部，但我试图获得更新的记录。有没有办法从底部选择最后的 X 条记录？

这就是我现在所拥有的。任何帮助不胜感激！

$SQLData=Read-SqlTableData -ServerInstance "Server" -Database "Database" -SchemaName "schema"  -tablename "table" -ColumnName "column","column2","column3" 

Answer 1

我这样做，运行查询并获取所需的确切数据。

try
{
$ConnectionString = "Server=$($DatabaseServer);Integrated Security=true;Initial Catalog=$($DatabaseName)"
$Query = "SELECT TOP $($BatchSize) * FROM [$($DatabaseName)].[dbo].[$($TableName)] WHERE $($colStatus) = 30"
Write-Host "DEBUG: `$Query is $Query " -ForegroundColor DarkYellow
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = $ConnectionString
$sqlConn.Open()
$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $sqlConn
$sqlcmd.CommandText = $Query
$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
$tbldata = New-Object System.Data.DataSet
$adp.Fill($tbldata) | Out-Null 
try {$sqlConn.Close()} catch {}
}
catch
{
$errorMsg = "ERROR: Cannot connect to Database $DatabaseName using the following Connection String $ConnectionString. Query is $Query. Get-KLMigrationSQLData."
}
if ($tbldata)
{                
Write-Host "DEBUG: Creating `$VDB Global Variable from Database Table: 
$TableName" -ForegroundColor DarkYellow
$dataFill = New-Variable -Name "VDB" -Force -PassThru -Scope Global
$dataFill.Value = @()
foreach ($DBRow in $tbldata.Tables.Rows)
{
$tempObj = New-Object System.Object
$tempObj | Add-Member -Type NoteProperty -Name TableName -Value $TableName 
$DBRows = $DBRow | Get-Member | Where-Object {$_.MemberType -eq 'Property' -or $_.MemberType -eq 'ParameterizedProperty'} 
ForEach ($Row in $DBRows){
if ($Row.Name -ne 'Item')
{
$colName = $Row.Name
$colValue = $DBRow.Item($colName)
$tempObj | Add-Member -Type NoteProperty -Name $colName -Value $colValue
}
} 
$dataFill.Value += $tempObj
}
}