PowerShell 5.1中如何传递Invoke-Sqlcmd的DataTable作为SQL参数

• PowerShell 5.1
• SQL Server 2016 及以上版本

是否可以将一次Invoke-Sqlcmd执行返回的DataTable类型结果，作为参数传入另一个SQL查询中使用？
此前尝试两种实现方案均运行失败：

方案1：直接在SQL字符串中拼接DataTable变量 #

$OutputTbl = Invoke-Sqlcmd -Query "select * from customer" -ServerInstance "(localdb)\MSSQLLocalDB" -Database "Database1" -OutputAs DataTables

$sql2 = "
DECLARE @OutputTbl TABLE (Id int, FirstName nvarchar(50), City nvarchar(50)) = $OutputTbl

Select * from @OutputTbl
"
$res2 = Invoke-Sqlcmd -Query $sql2 -ServerInstance "(localdb)\MSSQLLocalDB" -Database "Database1" -OutputAs DataTables

$res2

方案2：直接将DataTable作为普通ADO.NET参数传入 #

$dt = Invoke-Sqlcmd -Query "select * from customer" -ServerInstance "(localdb)\MSSQLLocalDB" -Database "Database1" -OutputAs DataTables

$sql2 = "
DECLARE @OutputTbl TABLE (Id int, FirstName nvarchar(50), City nvarchar(50)) 

INSERT INTO @OutputTbl
select * from @dt

select * from @OutputTbl
"
$params = 
@{
    'dt' = $dt 
} 
$conn = New-Connection '(localdb)\MSSQLLocalDB' -database 'Database1' 
$res2 = Invoke-Query -connection $conn -sql $sql2 -parameters $params

$res2

运行报错截图：

方案1错误点：PowerShell在双引号字符串中插入.NET对象时，会默认调用对象的ToString()方法，最终传入SQL的字符串是System.Data.DataTable，完全不符合T-SQL语法，无法被解析执行。
方案2错误点：SQL Server不支持将表结构数据作为普通参数传递，必须使用表值参数（TVP）实现，需要提前在数据库中定义和传入数据结构匹配的自定义表类型，同时显式标记参数类型为结构化类型，直接传DataTable对象会触发类型不匹配错误。

1. 首先在目标数据库Database1中创建匹配的自定义表类型，执行如下T-SQL：

CREATE TYPE dbo.CustomerTableType AS TABLE
(
    Id INT,
    FirstName NVARCHAR(50),
    City NVARCHAR(50)
)

2. 使用如下PowerShell代码传递DataTable作为参数执行第二个查询：

# 执行第一次查询获取DataTable结果
$dt = Invoke-Sqlcmd -Query "select * from customer" -ServerInstance "(localdb)\MSSQLLocalDB" -Database "Database1" -OutputAs DataTables

# 编写第二个查询语句，直接使用表值参数即可，无需额外声明表变量
$sql2 = "SELECT * FROM @customerData"

# 初始化数据库连接
$conn = New-Object System.Data.SqlClient.SqlConnection("Server=(localdb)\MSSQLLocalDB;Database=Database1;Integrated Security=True;")
$conn.Open()

# 构建查询命令
$cmd = $conn.CreateCommand()
$cmd.CommandText = $sql2

# 配置表值参数
$tvpParam = $cmd.Parameters.Add("@customerData", [System.Data.SqlDbType]::Structured)
$tvpParam.TypeName = "dbo.CustomerTableType"
$tvpParam.Value = $dt

# 执行查询获取结果
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
$result = New-Object System.Data.DataTable
$dataAdapter.Fill($result)

# 释放连接资源
$conn.Close()
$conn.Dispose()

# 输出查询结果
$result

可选替代方案：如果不想在数据库中创建自定义类型，可以遍历第一次返回的DataTable，逐行拼接成INSERT语句插入临时表后再查询，但该方案存在SQL注入风险，数据量较大时性能极差，仅适合临时测试场景使用。

内容的提问来源于stack exchange，提问作者Rod