使用Powershell插入数据 [英] Insert data using powershell

问题描述

我们有一个由插入语句组成的静态sql文件；基本上是测试数据。
是否可以在启用了Alwaysencrypted的azure sql db上使用powershell执行此脚本。我们使用密钥库来存储证书。

We have a static sql file which consists of insert statements;basically test data. is it possible to execute this script using powershell on azure sql db where alwaysencrypted is enabled. We use keyvault to store the certs.

推荐答案

不幸的是，PowerShell（Invoke-SqlCmd）不支持针对加密列的插入语句。这点。目前，Microsoft唯一支持此类语句的SQL工具是SSMS-请参阅： https://docs.microsoft.com/zh-cn/sql/relational-databases/security/encryption/configure-always -encrypted-using-sql-server-management-studio＃param 。

Unfortunately, PowerShell (Invoke-SqlCmd) does not support insert statements against encrypted columns at this point. The only SQL tool from Microsoft that supports such statements at this point is SSMS - please see: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-sql-server-management-studio#param .

另一种方法是将测试数据放入CSV文件并使用导入导出向导以将数据导入数据库。您可以将导入作业另存为SSIS包，您可以从命令行执行该包。这是一篇有关使用I / E向导从数据库导入（和加密）数据的博客文章（从文件导入将是类似的）。 https://blogs.msdn.microsoft.com/sqlsecurity/2015/10/31/ssms-encryption-wizard-enabling-always-encrypted-in-a-few-easy-steps/

An alternative could be to put your test data into a CSV file and use the Import Export Wizard to import the data into the database. You could save the import job as an SSIS package, which you could execute from the command line. Here is a blog article on using the I/E Wizard for importing (and encrypting) data from a database (importing from a file would be similar). https://blogs.msdn.microsoft.com/sqlsecurity/2015/10/31/ssms-encryption-wizard-enabling-always-encrypted-in-a-few-easy-steps/

Jakub

这篇关于使用Powershell插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！