为 SQL Server 表自动生成 INSERT 语句的最佳方法是什么?

2021-12-02 00:00:00 sql-server code-generation ssms

我们正在编写一个新的应用程序，在测试时，我们将需要一堆虚拟数据.我已经通过使用 MS Access 将 excel 文件转储到相关表中来添加该数据.

We are writing a new application, and while testing, we will need a bunch of dummy data. I've added that data by using MS Access to dump excel files into the relevant tables.

我们经常想要刷新"相关表，这意味着将它们全部删除，重新创建它们，并运行保存的 MS Access 追加查询.

Every so often, we want to "refresh" the relevant tables, which means dropping them all, re-creating them, and running a saved MS Access append query.

第一部分(删除和重新创建)是一个简单的 sql 脚本，但最后一部分让我感到畏缩.我想要一个包含一堆 INSERT 的设置脚本来重新生成虚拟数据.

The first part (dropping & re-creating) is an easy sql script, but the last part makes me cringe. I want a single setup script that has a bunch of INSERTs to regenerate the dummy data.

我现在有表格中的数据.从该数据集自动生成大量 INSERT 语句的最佳方法是什么?

I have the data in the tables now. What is the best way to automatically generate a big list of INSERT statements from that dataset?

我能想到的唯一方法是将表格保存到excel表中，然后编写一个excel公式为每一行创建一个INSERT，这肯定不是最好的方法.

The only way I can think of doing it is to save the table to an excel sheet and then write an excel formula to create an INSERT for every row, which is surely not the best way.

我使用 2008 Management Studio 连接到 SQL Server 2005 数据库.

I'm using the 2008 Management Studio to connect to a SQL Server 2005 database.

推荐答案

Microsoft 应该宣传 SSMS 2008 的此功能.您正在寻找的功能内置于 生成脚本 实用程序中，但功能默认情况下关闭，必须在编写表脚本时启用.

Microsoft should advertise this functionality of SSMS 2008. The feature you are looking for is built into the Generate Script utility, but the functionality is turned off by default and must be enabled when scripting a table.

这是为表中的所有数据生成 INSERT 语句的快速运行，不使用 SQL Management Studio 2008 的脚本或加载项:

This is a quick run through to generate the INSERT statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008:

  1. 右键单击数据库并转到任务 > 生成脚本.
  2. 选择要针对其生成脚本的表(或对象).
  3. 转到设置脚本选项标签，然后点击高级按钮.
  4. 在常规类别中，转到要编写脚本的数据类型
  5. 有 3 个选项:仅架构、仅数据和架构和数据.选择适当的选项并点击确定.
  1. Right-click on the database and go to Tasks > Generate Scripts.
  2. Select the tables (or objects) that you want to generate the script against.
  3. Go to Set scripting options tab and click on the Advanced button.
  4. In the General category, go to Type of data to script
  5. There are 3 options: Schema Only, Data Only, and Schema and Data. Select the appropriate option and click on OK.

然后，您将直接从 SSMS 中获得 CREATE TABLE 语句和所有 INSERT 语句用于数据.

You will then get the CREATE TABLE statement and all of the INSERT statements for the data straight out of SSMS.

相关文章