将Sqlcmd Join变量作为表调用

Question

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

我根据@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数据连接可能是一个选项。