调用-Sqlcmd连接变量作为表

Question

是否有方法将PS变量链接为调用-Sqlcmd中的表？我试过LEFT JOIN $psvar as p on T1.ID=P.ID

我根据DataTable的建议创建了一个@Cpt.Whale，从而更新了脚本。

# Define DataTable Columns
$tbl = New-Object system.Data.DataTable 'tbl_New'
$newcol = New-Object system.Data.DataColumn emplID,([string]); $tbl.columns.add($newcol)
$newcol = New-Object system.Data.DataColumn adGrp,([string]); $tbl.columns.add($newcol)

# Add data from Excel
$exelWkbk = '.\table.xlsx'
$excelQuery = '
        SELECT  F1 as emplID
                ,F2 as pcType
        FROM    [Sheet1$]
'
$queryOutput = Invoke-ExcelQuery $exelWkbk $excelQuery | Select-Object -Skip 1

$queryOutput | ForEach-Object {
    $row = $tbl.NewRow()
    $row.tmplID = ($_.emplID)
    $row.adGrp = ($_.pcType)
    $tbl.Rows.Add($row)
}

# Query SQL Data source joining Excel data
$sqlQuery = "
        USE SQLDATABASE;
        DECLARE @today as date = GETDATE();
        SELECT  emp.USER_ID
                ,wt.HOST
                ,a.pcType as DevicModel
        FROM    workstationTable as wt
                JOIN employeeTable as emp on wt.USER_ID = emp.USER_ID
                JOIN $tbl as a on emp.USER_ID = a.emplID
        WHERE   emp.NAME is not NULL
"
Invoke-Sqlcmd -Query $sqlQuery -ServerInstance 'dbName' |
Out-GridView

Answer 1

Powershell没有内置函数来执行不同数据集的SQL样式连接。对于简单的查找表类型，您可以使用Select-Object计算的属性，但我更喜欢使用构建在LINQ上的Join-Object模块：

# Gather both data sources into separate list variables:
$sqlData = Invoke-Sqlcmd -ServerInstance 'dbName' -Query '
  USE SQLDATABASE;
    DECLARE @today as date = GETDATE();
    SELECT  emp.USER_ID,wt.HOST
    FROM    workstationTable as wt
      JOIN  employeeTable as emp on wt.USER_ID = emp.USER_ID
    WHERE   emp.NAME is not NULL'
$excelData = Invoke-ExcelQuery $exelWkbk $excelQuery | Select-Object -Skip 1

# join the lists on a specific property
$joined = Join-Object -Type AllInLeft `
    -Left $sqlData    -LeftJoinProperty  USER_ID `
    -Right $excelData -RightJoinProperty EmplID 

$joined | Out-GridView

或者，由于您已经在使用Excel，所以文件中的SQL数据连接可能是一个选项。