为SQL Server表自动生成INSERT语句的最佳方法是什么？ [英] What is the best way to auto-generate INSERT statements for a SQL Server table?

问题描述

我们正在编写一个新的应用程序，并且在测试期间，我们将需要一堆虚拟数据。我通过使用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的此功能。要查找的功能内置在 Generate Script （生成脚本）工具，但默认情况下该功能处于关闭状态，在编写表脚本时必须启用该功能。

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.

然后您将获得 CREATE TABLE 语句以及所有 INSERT 语句，用于直接从SSMS中获取数据。

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

这篇关于为SQL Server表自动生成INSERT语句的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！