使用带有表值参数的存储过程，使用PowerShell填充时态SQL DB表

Question

虽然熟悉Powershell，但我正在学习SQL。

我正在尝试使用表值参数调用一个存储过程，以将PowerShell中的一组数据插入临时SQL DB表中。

我正在使用SqlServer PowerShell模块：https://learn.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-服务器-ver15

DB被称为"；WAGS"；，该表被称为"；AzureADUsers"；并且我的用户定义类型被称为"；AzureADUser"；

命令似乎成功完成了，尽管我在表中没有看到任何数据，我不确定我做错了什么，或者这是正确的方法。

我的用户定义类型如下(我的AzureADUser表中的列也是如此(。

01: ImmutableId         varchar            255 UNIQUE
02: DisplayName         varchar            255
03: UserPrincipalName   varchar            255 UNIQUE
04: ObjectId            uniqueidentifier       16  UNIQUE

我的存储过程是这样的(我已经包含了整个ALTER脚本，以防我在这里犯了错误(。

USE [WAGS]
GO
/****** Object:  StoredProcedure [dbo].[spUpdateAzureADUsersTable]    Script Date: 20/10/2020 15:59:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUpdateAzureADUsersTable] (@users [dbo].[AzureADUser] READONLY)
AS
INSERT INTO [dbo].[AzureADUsers]
(
ImmutableId,
DisplayName,
UserPrincipalName,
ObjectId
)
SELECT * FROM @users

我正在尝试使用Invoke-SqlCmd PowerShell cmdlet调用存储过程，如下所示：

$azureAdUsers = Get-AzureADUser -All $true | ? { $_.ImmutableId }
$data = $azureAdUsers | Select ImmutableId,DisplayName,UserPrincipalName,ObjectId
Invoke-Sqlcmd -ServerInstance 'MERCURYSQLEXPRESS' -Database 'WAGS' -QUERY "EXEC spUpdateAzureADUsersTable $data"

Answer 1

发现Microsoft SQL PowerShell模块存在许多错误/限制。存储过程、类型和表都创建正确，问题是我如何使用PowerShell调用它(或者更确切地说，我如何传递值数组(。

在这个家伙的帮助下，我最终用Powershell进入了ADO.NET：http://tomaslind.net/2016/05/27/from-powershell-to-sql-tables/

注意：当创建大型数据表时，使用[void]比管道输出Null更快。

我的PowerShell现在看起来是这样的：

$azureAdUsers = Get-AzureADUser -All $true | ? { $_.ImmutableId }
$dataTable = [System.Data.DataTable]::New()
[void]($dataTable.Columns.Add("ObjectId","System.Guid"))
[void]($dataTable.Columns.Add("ImmutableId","System.String"))
[void]($dataTable.Columns.Add("DisplayName","System.String"))
[void]($dataTable.Columns.Add("UserPrincipalName","System.String"))
foreach($u in $azureAdUsers)
{
[void]($dataTable.Rows.Add($u.ObjectId,$u.ImmutableId,$u.DisplayName,$u.UserPrincipalName))
}
$ServerInstance = "MERCURYSQLEXPRESS"
$DatabaseName = "WAGS"

$ConnString = "Server=$ServerInstance;Database=$DatabaseName;Integrated Security=SSPI;"

$conn = New-Object System.Data.SqlClient.SqlConnection $ConnString
$conn.Open()

$query = "dbo.spUpdateAzureADUsersTable"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmd.CommandText = $Query
$cmd.Parameters.Add("@users", [System.Data.SqlDbType]::Structured) | Out-Null
$cmd.Parameters["@users"].Value = $dataTable
$cmd.ExecuteNonQuery() | Out-Null
$conn.Close()