我正在尝试使用Write-SqlTableData写入表。名称已更改，因为它是公司数据库。
环境：
- Windows 8.1 with Powershell 5.0， SqlServer module 21.0.17099
命令：
Write-SqlTableData -DatabaseName 'dbname' -ServerInstance sql1 -TableName tablename -InputData (1,'1346',$false,'CalendarInvite') -Force -SchemaName "dbo"
错误：Write-SqlTableData : Input array is longer than the number of columns in this table.
表列：
- ID：整数标识
- 范围：整数
- 上下文 ID： ntext
- 功能启用：位
- 类型：文本
我不知道为什么我得到错误。四个值应该足够了，我按列顺序给出它们。
我也尝试使用哈希表，但错误更令人困惑。
-
哈希表：
@{Scope=1;ContextID='1346';FeatureEnabled=$false;Type='CalendarInvite'} -
错误：
Write-SqlTableData : The given value of type String from the data source cannot be converted to type int of the specified target column.
我完全糊涂了。我的命令有什么问题？
编辑：该命令适用于我的测试 SQL (13.0.1601.5)，但团队测试 SQL (11.0.3156.0)。也许版本很重要？
@Matt @DSakura：所以，我把头撞在墙上很久，但终于想通了......有点。我甚至反编译了包含 Write-SqlTableData cmdlet (Microsoft.SqlServer.Management.PSSnapins) 的程序集。
这是我发现的：
这是一个错误。在代码中，cmdlet 尝试猜测你尝试向其抛出的内容，然后尝试将你发送的内容重新格式化为与 SQL 预期接收的内容一致的内容。
它在这个过程中进行：
1 向我发送了哪个 .Net 对象？
- 它是 SQL 数据集吗？
- 它是一个 SQL 数据表吗？
- 它是 SQL 数据行吗？
- 是其他类型的时髦对象吗？
2 如果时髦的对象，我可以把它变成我可以放入SQL表的东西吗？
.Net 对象不与 SQL 列类型对齐([System.String] 不是有效的 SQL 类型)，因此 cmdlet 使用转换表进行最佳猜测(例如 nvarchar(MAX))，然后在转换类型后尝试将其转储到表中。
当您使用"-force"参数创建全新的表时，这更容易，因为它不必将您尝试输入的内容与现有表中已存在的列相匹配。它只是做出最佳猜测，并将数据类型更改为SQL理解和采用的数据类型。
例子：
哈希表
Write-SQLTableData -TableName TableHash -SchemaName dbo -DatabaseName DB1 -ServerInstance localhostSQLExpress -InputData @{col1="SomeString"} -force
生产：
-------------------------- | |钥匙 |价值 | -------------------------- | 1 |Col1 |某串 | --------------------------
类型：
------------------------------------------- |列名称 |数据类型 |允许空值 | ------------------------------------------- | [键] |sql_varient | |------------------------------------------- | 价值 |sql_varient | | -------------------------------------------
PSObject
$Obj = New-Object PSObject -Property @{col1="SomeString"}
Write-SQLTableData -TableName TablePSObject -SchemaName dbo -DatabaseName DB1 -ServerInstance localhostSQLExpress -InputData $Obj -force
生产：
------------------- | |Col1 | ------------------- | 1 |某串 | -------------------
带类型：
--------------------------------------------- |列名称 |数据类型 |允许空值 | --------------------------------------------- | Col1 |恩瓦尔查尔(最大) | 真 |---------------------------------------------
所以，这就是它变得复杂的地方。当你有一个现有表时，该 cmdlet 会尽最大努力将数据与表列对齐。它还不是那么擅长。文档提供的默认示例演示如何通过将值放入哈希表中并将列名作为哈希键来执行此操作。这不适用于现有表官方文档在这里：https://learn.microsoft.com/en-us/powershell/module/sqlserver/write-sqltabledata?view=sqlserver-ps
现有表失败示例
现有表：
--------------------------------------------- |列名称 |数据类型 |允许空值 | --------------------------------------------- | Col1 |瓦尔查尔(最大) | 假 | --------------------------------------------- | Col2 |文本 | 假 |---------------------------------------------
PowerShell 命令：
Write-SQLTableData -TableName TableExistsAlready -SchemaName dbo -DatabaseName DB1 -ServerInstance localhostSQLExpress -InputData @{col1="SomeText";col2="SomeMoreText"}
Write-SQLTableData -TableName TableExistsAlready -SchemaName dbo -DatabaseName DB1 -ServerInstance localhostSQLExpress -InputData @{col1="SomeText2";col2="SomeMoreText2"}
生产：
----------------------------- | |Col1 |Col2 | ----------------------------- | 1 |Col2 |更多文本 | ----------------------------- | 2 |Col1 |某某文本 | ----------------------------- | 3 |Col2 |更多文本2 | ----------------------------- | 4 |Col1 |一些文本2 | -----------------------------
不用说，这不是我们所期望的。
当您尝试将更复杂的数据类型放入表中时，情况会变得更糟，因为即使您在哈希表中排列了正确的类型(带有字符串的字符串，带有整数的整数等)，它也永远不会在写入 SQL 阶段正确排列列。
那么，解决方案是什么？
在这里。。。该代码实际上是为处理类型 [PSCustomObject] 而编写的。
如果您想了解 PSObject、Hash 和 PSCustomObject 类型之间的详细差异，请参阅以下内容： PSObject、Hashtable 和 PSCustomObject 之间的区别
因此，既然我们知道了我们需要什么，同样重要的是要注意哈希表不设置项目的顺序(至少默认情况下不会)。但是，您可以指定 PSObject 类型的顺序，但不能使用快速"哈希"创建方法。您必须单独定义每个 NoteProperty。请参见：更改对象中列的顺序
但是，从 PowerShell V3 开始，Microsoft为 [PSCustomObject] 添加了一个类型加速器，该加速器使用有序哈希表创建对象，因此属性将保持声明顺序。幸运的是，这就是我们想要的类型。
所以，让我们再做一次：
现有表工作示例
现有表(注意，我保留了原始数据)：
---------------------------------------------|列名称 |数据类型 |允许空值 | --------------------------------------------- | Col1 |瓦尔查尔(最大) | 假 | --------------------------------------------- | Col2 |文本 | 假 | ---------------------------------------------
PowerShell 命令：
$Obj = [PSCustomObject] @{col1="SomeText";col2="SomeMoreText"}
Write-SQLTableData -TableName TableExistsAlready -SchemaName dbo -DatabaseName DB1 -ServerInstance localhostSQLExpress -InputData $Obj
$Obj2 = [PSCustomObject] @{col1="SomeText2";col2="SomeMoreText2"}
Write-SQLTableData -TableName TableExistsAlready -SchemaName dbo -DatabaseName DB1 -ServerInstance localhostSQLExpress -InputData $Obj2
生产：
---------------------------------- | |Col1 |Col2 | ---------------------------------- | 1 |Col2 |更多文本 | ---------------------------------- | 2 |Col1 |某某文本 | ---------------------------------- | 3 |Col2 |更多文本2 | ---------------------------------- | 4 |Col1 |一些文本2 | ---------------------------------- | 5 |某某文本 |更多文本 | ---------------------------------- | 6 |一些文本2 |更多文本2 |----------------------------------
呜！它实际上是按正确的顺序排列的！现在，不要忘记，如果你有一个需要 int 的列，你需要确保你的输入与它所期望的一致。我将把 cmdlet 用于比较类型的转换表放在底部。我从编译的 dll 中反汇编了代码，因此无法保证准确的准确性。
因此，现在让我们转到"id"(自动递增身份规范)列问题。如果不在 PSCustomObject 中添加该列，则 SQL 写入将被拒绝。但是，没问题！事实证明，我们可以添加 id 列名，只需将值留空，它就会以 SQL 将下一个值放入的方式进行翻译。
ID 列示例
现有表(注意，这是一个新的空白表，只有列，没有行数据)：
--------------------------------------------- |列名称 |数据类型 |允许空值 |--------------------------------------------- | 身份证 |国际 | 假 | --------------------------------------------- | Col1 |瓦尔查尔(50) | 假 | --------------------------------------------- | Col2 |文本 | 假 | --------------------------------------------- 注意："id"列将"身份规范(是身份)"转换为"是"，"身份增量"设置为 1，"身份种子"设置为 1。
PowerShell 命令：
$Obj = [PSCustomObject] @{
id=''
col1="SomeString"
col2="SomeMoreString"
}
Write-SQLTableData -TableName TableWithID -SchemaName dbo -DatabaseName DB1 -ServerInstance localhostSQLExpress -InputData $Obj
生产：
-------------------------------------- | |身份证 |Col1 |Col2 | -------------------------------------- | 1 |1 |某某文本 |更多文本 | --------------------------------------
你说什么？再执行四次命令会发生什么？我很高兴你问： PowerShell 命令：
$Obj = [PSCustomObject] @{
id=''
col1="SomeString"
col2="SomeMoreString"
}
Write-SQLTableData -TableName TableWithID -SchemaName dbo -DatabaseName DB1 -ServerInstance localhostSQLExpress -InputData $Obj
Write-SQLTableData -TableName TableWithID -SchemaName dbo -DatabaseName DB1 -ServerInstance localhostSQLExpress -InputData $Obj
Write-SQLTableData -TableName TableWithID -SchemaName dbo -DatabaseName DB1 -ServerInstance localhostSQLExpress -InputData $Obj
Write-SQLTableData -TableName TableWithID -SchemaName dbo -DatabaseName DB1 -ServerInstance localhostSQLExpress -InputData $Obj
生产：
-------------------------------------- | |身份证 |Col1 |Col2 |-------------------------------------- | 1 |1 |某某文本 |更多文本 | -------------------------------------- | 2 |2 |某某文本 |更多文本 | -------------------------------------- | 3 |3 |某某文本 |更多文本 | -------------------------------------- | 4 |4 |某某文本 |更多文本 | -------------------------------------- | 5 |5 |某某文本 |更多文本 | --------------------------------------
嗯...这比我想象的要长。我希望这对其他人有所帮助。
这是转换表：
类型换算表
case "System.String":
return DataType.NVarCharMax;
case "System.Int64":
return DataType.BigInt;
case "System.Byte[]":
return DataType.VarBinaryMax;
case "System.Boolean":
return DataType.Bit;
case "System.Char":
return DataType.NChar(1);
case "System.DateTime":
return DataType.DateTime2(7);
case "System.DateTimeOffset":
return DataType.DateTimeOffset(7);
case "System.Decimal":
return DataType.Decimal(14, 0x1c);
case "System.Double":
return DataType.Float;
case "System.Int32":
return DataType.Int;
case "System.Char[]":
return DataType.NVarCharMax;
case "System.Single":
return DataType.Real;
case "System.Int16":
return DataType.SmallInt;
case "System.Object":
return DataType.Variant;
case "System.TimeSpan":
return DataType.Timestamp;
case "System.Byte":
return DataType.TinyInt;
case "System.Guid":
return DataType.UniqueIdentifier;
case "System.UInt64":
return DataType.Numeric(0, 20);
case "System.UInt32":
return DataType.BigInt;
case "System.UInt16":
return DataType.Int;
case "System.SByte":
return DataType.SmallInt;
case "Microsoft.SqlServer.Types.SqlHierarchyId":
return DataType.HierarchyId;
case "Microsoft.SqlServer.Types.SqlGeography":
return DataType.Geography;
case "Microsoft.SqlServer.Types.SqlGeometry":
return DataType.Geometry;