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

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将excel文件转储到相关表中，添加了这些数据。

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.

通常，我们希望“刷新”相关表，这意味着将它们全部删除，重新创建它们，并运行一个保存的MS Access append查询。

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.

第一部分(删除和重新创建)是一个简单的sql脚本，但是最后一部分让我感到畏缩。我想要一个单独的安装脚本，它有一堆插入来重新生成虚拟数据。

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?

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

I'm thinking of something like in TOAD (for Oracle) where you can right-click on a grid and click Save As->Insert Statements, and it will just dump a big sql script wherever you want.

我在想，像在蛤蟆(Oracle)中，你可以右键单击一个网格，然后点击Save As->插入语句，它会在你想要的任何地方转储一个大的sql脚本。

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.

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

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

我正在使用2008管理工作室连接到一个SQL Server 2005数据库。

19 个解决方案

#1

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.

微软应该对SSMS 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:

这是为表中所有数据生成INSERT语句的快捷方式，无需使用脚本或外接程序到SQL Management Studio 2008:

1. Right-click on the database and go to Tasks > Generate Scripts.
2. 右键单击数据库并执行>生成脚本的任务。
3. Select the tables (or objects) that you want to generate the script against.
4. 选择希望生成脚本的表(或对象)。
5. Go to Set scripting options tab and click on the Advanced button.
6. 点击“设置脚本选项”选项卡，点击“高级”按钮。
7. In the General category, go to Type of data to script
8. 在一般类别中，将数据类型转换为脚本。
9. There are 3 options: Schema Only, Data Only, and Schema and Data. Select the appropriate option and click on OK.
10. 有3种选项:模式、数据、模式和数据。选择适当的选项并单击OK。

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

然后，您将获得CREATE TABLE语句和所有从ssm中直接获得数据的INSERT语句。

#2

We use this stored procedure - it allows you to target specific tables, and use where clauses. You can find the text here.

我们使用这个存储过程——它允许您针对特定的表，并使用where子句。你可以在这里找到文本。

For example, it lets you do this: To generate INSERT statements for table 'titles':

例如，它允许您这样做:为表的标题生成INSERT语句:

EXEC sp_generate_inserts 'titles'

#3

As mentioned by @Mike Ritacco but updated for SSMS 2008 R2

如@Mike Ritacco所提到的，但是更新了SSMS 2008 R2。

1. Right click on the database name
2. 右键单击数据库名称。
3. Choose Tasks > Generate scripts
4. 选择任务>生成脚本。
5. Depending on your settings the intro page may show or not
6. 根据您的设置，intro页面可能显示或不显示。
7. Choose 'Select specific database objects',
8. 选择“选择特定的数据库对象”，
9. Expand the tree view and check the relevant tables
10. 展开树视图并检查相关的表。
11. Click Next
12. 单击Next
13. Click Advanced
14. 单击高级
15. Under General section, choose the appropriate option for 'Types of data to script'
16. 在“一般”部分，选择“数据类型”的适当选项
17. Complete the wizard
18. 完成向导

You will then get all of the INSERT statements for the data straight out of SSMS.

然后，您将从ssm中直接获取数据的所有INSERT语句。

EDIT 2016-10-25 SQL Server 2016/SSMS 13.0.15900.1

编辑2016-10-25 SQL Server 2016/SSMS 13.0.15900.1。

1. Right click on the database name

   右键单击数据库名称。

2. Choose Tasks > Generate scripts

   选择任务>生成脚本。

3. Depending on your settings the intro page may show or not

   根据您的设置，intro页面可能显示或不显示。

4. Choose 'Select specific database objects',

   选择“选择特定的数据库对象”，

5. Expand the tree view and check the relevant tables

   展开树视图并检查相关的表。

6. Click Next

   单击Next

7. Click Advanced

   单击高级

8. Under General section, choose the appropriate option for 'Types of data to script'

   在“一般”部分，选择“数据类型”的适当选项

9. Click OK

   单击OK

10. Pick whether you want the output to go to a new query, the clipboard or a file

    选择是否想要输出到新的查询、剪贴板或文件。

11. Click Next twice

    单击Next两次

12. Your script is prepared in accordance with the settings you picked above

    您的脚本是按照您在上面选择的设置编写的。

13. Click Finish

    单击Finish

#4

You can use SSMS Tools Pack (available for SQL Server 2005 and 2008). It comes with a feature for generating insert statements.

您可以使用SSMS工具包(用于SQL Server 2005和2008)。它具有生成insert语句的特性。

http://www.ssmstoolspack.com/

http://www.ssmstoolspack.com/

#5

I'm using SSMS 2008 version 10.0.5500.0. In this version as part of the Generate Scripts wizard, instead of an Advanced button, there is the screen below. In this case, I wanted just the data inserted and no create statements, so I had to change the two circled properties

我正在使用SSMS 2008版10.0.5500.0。在这个版本中，作为生成脚本向导的一部分，而不是一个高级按钮，下面是屏幕。在这种情况下，我只需要插入数据，而不需要创建语句，所以我必须改变这两个被包围的属性。

#6

This can be done using Visual Studio too (at least in version 2013 onwards).

这也可以使用Visual Studio(至少在2013年的版本中)。

In VS 2013 it is also possible to filter the list of rows the inserts statement are based on, this is something not possible in SSMS as for as I know.

在VS 2013中，也可以过滤插入语句所基于的行列表，这在SSMS中是不可能的，就像我所知道的那样。

Perform the following steps:

执行以下步骤:

• Open the "SQL Server Object Explorer" window (menu: /View/SQL Server Object Explorer)
• 打开“SQL Server Object Explorer”窗口(菜单:/视图/SQL Server对象资源管理器)
• Open / expand the database and its tables
• 打开/展开数据库及其表。
• Right click on the table and choose "View data" from context menu
• 右键单击表格，从上下文菜单中选择“查看数据”。
• This will display the data in the main area
• 这将在主区域显示数据。
• Optional step: Click on the filter icon "Sort and filter data set" (the fourth icon from the left on the row above the result) and apply some filter to one or more columns
• 可选步骤:点击滤镜图标“排序和过滤数据集”(在结果上方的行左边的第四个图标)，并将一些过滤器应用到一个或多个列上。
• Click on the "Script" or "Script to File" icons (the icons on the right of the top row, they look like little sheets of paper)
• 点击“脚本”或“脚本文件”图标(顶部行右侧的图标，它们看起来就像一张小纸片)

This will create the (conditional) insert statements for the selected table to the active window or file.

这将为所选的表创建(条件)插入语句到活动窗口或文件。

The "Filter" and "Script" buttons Visual Studio 2013:

“过滤器”和“脚本”按钮Visual Studio 2013:

#7

Jane Dallaway's stored procedure: http://docs.google.com/leaf?id=0B_AkC4ZdTI9tNWVmZWU3NzAtMWY1My00NjgwLWI3ZjQtMTY1NDMxYzBhYzgx&hl=en_GB. Documentation is a series of blog posts: https://www.google.com/search?q=spu_generateinsert&as_sitesearch=http%3A%2F%2Fjane.dallaway.com

Jane Dallaway的存储过程:http://docs.google.com/leaf?id= 0b_akc4zdti9tnwvmzatmwy1my00njgqtmty1ndmxyzbhyzgx&hl= en_gb。文档是一系列的博客文章:https://www.google.com/search?

#8

The first link to sp_generate_inserts is pretty cool, here is a really simple version:

sp_generate_insert的第一个链接非常酷，这里有一个非常简单的版本:

DECLARE @Fields VARCHAR(max); SET @Fields = '[QueueName], [iSort]' -- your fields, keep []
DECLARE @Table  VARCHAR(max); SET @Table  = 'Queues'               -- your table

DECLARE @SQL    VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Fields + ')'') + CHAR(13) + CHAR(10) + 
 ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Fields, ',', ' + '', '' + '), '[', ''''''''' + CAST('),']',' AS VARCHAR(max)) + ''''''''') +' FROM ' + @Table + '
PRINT @S'

EXEC (@SQL)

On my system, I get this result:

在我的系统中，我得到了这个结果:

INSERT INTO Queues([QueueName], [iSort])
SELECT 'WD: Auto Capture', '10' UNION 
SELECT 'Car/Lar', '11' UNION 
SELECT 'Scan Line', '21' UNION 
SELECT 'OCR', '22' UNION 
SELECT 'Dynamic Template', '23' UNION 
SELECT 'Fix MICR', '41' UNION 
SELECT 'Fix MICR (Supervisor)', '42' UNION 
SELECT 'Foreign MICR', '43' UNION 
...

#9

If you need a programmatic access, then you can use an open source stored procedure `GenerateInsert.

如果您需要一个编程访问，那么您可以使用一个开源存储过程的GenerateInsert。

INSERT statement(s) generator

INSERT语句(s)发电机

Just as a simple and quick example, to generate INSERT statements for a table AdventureWorks.Person.AddressType execute following statements:

就像一个简单快速的例子一样，为一个表AdventureWorks.Person生成INSERT语句。AddressType执行以下语句:

USE [AdventureWorks];
GO
EXECUTE dbo.GenerateInsert @ObjectName = N'Person.AddressType';

This will generate the following script:

这将生成以下脚本:

SET NOCOUNT ON
SET IDENTITY_INSERT Person.AddressType ON
INSERT INTO Person.AddressType
([AddressTypeID],[Name],[rowguid],[ModifiedDate])
VALUES
 (1,N'Billing','B84F78B1-4EFE-4A0E-8CB7-70E9F112F886',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(2,N'Home','41BC2FF6-F0FC-475F-8EB9-CEC0805AA0F2',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(3,N'Main Office','8EEEC28C-07A2-4FB9-AD0A-42D4A0BBC575',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(4,N'Primary','24CB3088-4345-47C4-86C5-17B535133D1E',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(5,N'Shipping','B29DA3F8-19A3-47DA-9DAA-15C84F4A83A5',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(6,N'Archive','A67F238A-5BA2-444B-966C-0467ED9C427F',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
SET IDENTITY_INSERT Person.AddressType OFF

#10

Perhaps you can try the SQL Server Publishing Wizard http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

也许你可以试试SQL Server发布向导http://www.microsoft.com/downloads/details.aspx?

It has a wizard that helps you script insert statements.

它有一个向导，可以帮助您编写插入语句。

#11

My contribution to the problem, a Powershell INSERT script generator that lets you script multiple tables without having to use the cumbersome SSMS GUI. Great for rapidly persisting "seed" data into source control.

我对这个问题的贡献，一个Powershell插入脚本生成器，它可以让您不必使用笨重的SSMS GUI就可以编写多个表。非常适合快速将“种子”数据保存到源代码控制中。

1. Save the below script as "filename.ps1".
2. 将下面的脚本保存为“filename.ps1”。
3. Make your own modifications to the areas under "CUSTOMIZE ME".
4. 对“定制我”的区域进行自己的修改。
5. You can add the list of tables to script in any order.
6. 您可以在任意顺序中添加表的列表。
7. You can open the script in Powershell ISE and hit the Play button, or simply execute the script in the Powershell command prompt.
8. 您可以在Powershell ISE中打开脚本并单击Play按钮，或者在Powershell命令提示符中执行脚本。

By default, the INSERT script generated will be "SeedData.sql" under the same folder as the script.

默认情况下，生成的插入脚本将是“种子数据”。sql“在与脚本相同的文件夹下”。

You will need the SQL Server Management Objects assemblies installed, which should be there if you have SSMS installed.

您将需要安装的sqlserver管理对象程序集，如果安装了SSMS，就应该安装它。

Add-Type -AssemblyName ("Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
Add-Type -AssemblyName ("Microsoft.SqlServer.ConnectionInfo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")



#CUSTOMIZE ME
$outputFile = ".\SeedData.sql"
$connectionString = "Data Source=.;Initial Catalog=mydb;Integrated Security=True;"



$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$srv = new-object Microsoft.SqlServer.Management.Smo.Server($conn)
$db = $srv.Databases[$srv.ConnectionContext.DatabaseName]
$scr = New-Object Microsoft.SqlServer.Management.Smo.Scripter $srv
$scr.Options.FileName = $outputFile
$scr.Options.AppendToFile = $false
$scr.Options.ScriptSchema = $false
$scr.Options.ScriptData = $true
$scr.Options.NoCommandTerminator = $true

$tables = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection



#CUSTOMIZE ME
$tables.Add($db.Tables["Category"].Urn)
$tables.Add($db.Tables["Product"].Urn)
$tables.Add($db.Tables["Vendor"].Urn)



[void]$scr.EnumScript($tables)

$sqlConnection.Close()

#12

Don't use inserts, use BCP

不要使用插入，使用BCP。

#13

I used this script which I have put on my blog (How-to generate Insert statement procedures on sql server).

我使用了我在博客上写的这个脚本(如何在sql server上生成Insert语句过程)。

So far has worked for me, although they might be bugs I have not discovered yet .

到目前为止，我一直在为我工作，尽管他们可能是我还没有发现的bug。

#14

GenerateData is an amazing tool for this. It's also very easy to make tweaks to it because the source code is available to you. A few nice features:

GenerateData是一个很好的工具。它也很容易对它进行调整，因为源代码是可用的。一些不错的功能:

• Name generator for peoples names and places
• 人名和地名生成器。
• Ability to save Generation profile (after it is downloaded and set up locally)
• 能够保存生成概要文件(在下载后并在本地设置)
• Ability to customize and manipulate the generation through scripts
• 能够通过脚本自定义和操作生成。
• Many different outputs (CSV, Javascript, JSON, etc.) for the data (in case you need to test the set in different environments and want to skip the database access)
• 对于数据，有许多不同的输出(CSV、Javascript、JSON等)(如果您需要在不同的环境中测试集合，并希望跳过数据库访问)
• Free. But consider donating if you find the software useful :).
• 免费的。但是如果你觉得这个软件很有用，可以考虑捐赠。

#15

I use sqlite to do this. I find it very, very useful for creating scratch/test databases.

我用sqlite来做这个。我发现它对创建scratch/测试数据库非常有用。

sqlite3 foo.sqlite .dump > foo_as_a_bunch_of_inserts.sql

sqlite3 foo。sqlite .dump > foo_as_a_bunch_of_inserts.sql

#16

Do you have data in a production database yet? If so, you could setup a period refresh of the data via DTS. We do ours weekly on the weekends and it is very nice to have clean, real data every week for our testing.

在生产数据库中有数据吗?如果是这样，可以通过DTS设置一个周期刷新数据。我们每周都在周末做我们的工作，每周都有干净的、真实的数据进行测试，这是非常好的。

If you don't have production yet, then you should create a database that is they want you want it (fresh). Then, duplicate that database and use that newly created database as your test environment. When you want the clean version, simply duplicate your clean one again and Bob's your uncle.

如果您还没有生产，那么您应该创建一个他们想要的数据库(新鲜的)。然后，复制该数据库并使用新创建的数据库作为测试环境。当你想要干净的版本时，简单地复制你的清洁版，然后Bob是你的叔叔。

#17

why not just backup the data before your work with it, then restore when you want it to be refreshed?

为什么不只是在工作之前备份数据，然后在需要刷新的时候恢复呢?

if you must generate inserts try: http://vyaskn.tripod.com/code.htm#inserts

如果必须生成插入操作，请尝试:http://vyaskn.tripod.com/code.htm#insert。

#18

Not sure, if I understand your question correctly.

不确定，如果我能正确理解你的问题。

If you have data in MS-Access, which you want to move it to SQL Server - you could use DTS.
And, I guess you could use SQL profiler to see all the INSERT statements going by, I suppose.

如果您在MS-Access中有数据，您希望将其移动到SQL Server—您可以使用DTS。而且，我想您可以使用SQL profiler来查看所有的INSERT语句。

#19

I have also researched lot on this, but I could not get the concrete solution for this. Currently the approach I follow is copy the contents in excel from SQL Server Managment studio and then import the data into Oracle-TOAD and then generate the insert statements

对此我也进行了大量的研究，但我无法得到具体的解决方案。目前我所遵循的方法是将excel中的内容复制到SQL Server Managment studio中，然后将数据导入Oracle-TOAD，然后生成insert语句。