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

问题描述：

我们正在编写一个新的应用程序，在测试时，我们需要一堆虚拟数据。我已经使用MS Access将这些数据添加到相关表格中。什么是为SQL Server表自动生成INSERT语句的最佳方法？

每隔一段时间，我们都希望“刷新”相关表格，这意味着全部删除它们，重新创建它们，并运行已保存的MS Access追加查询。

第一部分（删除&重新创建）是一个简单的SQL脚本，但最后一部分让我畏缩。我想要一个包含一堆INSERT的安装脚本来重新生成虚拟数据。

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

我想到的东西就像TOAD（对于Oracle），你可以右键单击一个网格，然后单击另存为 - >插入语句，它只会在你想要的地方转储一个大的SQL脚本。

我能想到这样做的唯一事情是这样的表保存到一个Excel工作表，然后写Excel公式创建的每一行，这肯定不是最好的办法一个INSERT。

我正在使用2008 Management Studio连接到SQL Server 2005数据库。

哇，我刚刚检查了我的安装，你说得对，“脚本表作为” - >“插入”只给你一个插入模板，而不是插入的一个页面，你实际数据！我希望你的问题得到解答，因为我想要一个简单的方法去做你也在问的事情。 – JoeCool 2009-06-11 17:46:52

答

脚本表时，微软应该宣传的2008年SSMS你正在寻找的是内置在生成脚本实用的功能，这样的功能，但功能是默认关闭的，并且必须启用。

这是一个快速运行通过生成所有表格中的数据的INSERT语句，使用任何脚本或加载项到SQL Management Studio中2008：

在数据库上单击鼠标右键，去任务>生成脚本。
选择要生成脚本的表（或对象）。
转至设置脚本选项选项卡并单击高级按钮。
在一般类别，然后转至类型数据的脚本
有3种选择：仅架构，数据只有，并模式和数据。选择适当的选项并点击确定。

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

答

为什么不只是备份与它的工作之前的数据，然后还原，当你希望它被刷新？

，如果你必须生成插入尝试：http://vyaskn.tripod.com/code.htm#inserts

我想要灵活地编辑INSERT中的数据，如果我想。除此之外，没有真正的理由......我需要研究RESTORE和BACKUP的语法，所以我可以通过脚本来完成。 – JosephStyons 2009-06-11 17:46:22

答

我们使用这个存储过程 - 它允许你针对特定表，并且使用WHERE子句。你可以找到文字here。

例如，它可以让你做到这一点：要生成表“标题” INSERT语句：

EXEC sp_generate_inserts 'titles'

这对我来说非常合适，只不过生成的INSERT在末尾没有分号。我补充说，并成功地使用它。感谢您回答qstn！ – JosephStyons 2009-06-11 19:57:36

我很高兴它为你效力！ – 2009-06-11 20:21:02

哇...方便的小脚本....精彩 – CMB 2009-07-17 19:44:28

答

不要用刀片，使用BCP

答

不知道，如果我理解你的问题正确。

如果MS-Access中有数据要移动到SQL Server中，则可以使用DTS。
而且，我想你可以使用SQL分析器查看所有的INSERT语句。

答

您是否有生产数据库中的数据？如果是这样，您可以通过DTS设置数据的周期刷新。我们每周都会在周末做我们的工作，我们每周都会为我们的测试提供干净，真实的数据，这是非常好的。

如果您还没有生产，那么您应该创建一个他们想要的数据库（新鲜）。然后，复制该数据库并将该新创建的数据库用作您的测试环境。当你想要干净的版本，只需复制你的干净的一个，并Bob's your uncle。

答

第一个链接到sp_generate_inserts是很酷，这里是一个非常简单的版本：

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)

在我的系统，我得到这样的结果：

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 
...

答

或许你可以尝试在SQL Server发布向导 http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

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

答

您可以使用SSMS Tools Pack（适用于SQL Server 2005和2008）。它带有一个用于生成插入语句的功能。

http://www.ssmstoolspack.com/

答

我也研究了这个地段，但我不能得到这个具体的解决方案。目前我所遵循的方法是从SQL Server Managment studio复制excel中的内容，然后将数据导入Oracle-TOAD，然后生成插入语句

答

Jane Dallaway的存储过程：http://docs.google.com/leaf?id=0B_AkC4ZdTI9tNWVmZWU3NzAtMWY1My00NjgwLWI3ZjQtMTY1NDMxYzBhYzgx&hl=en_GB。文档是一系列的博客文章：https://www.google.com/search?q=spu_generateinsert&as_sitesearch=http%3A%2F%2Fjane.dallaway.com

答

我使用sqlite来做到这一点。我发现它对创建scratch/test数据库非常有用。

sqlite3 foo.sqlite .dump > foo_as_a_bunch_of_inserts.sql

答

我用这个script，我把它放在我的博客上（如何在sql server上生成插入语句程序）。

到目前为止我已经为我工作，虽然他们可能是我还没有发现的错误。

答

正如@Mike Ritacco提及，但更新2008年SSMS R2

上的数据库名称
选择任务右键点击>生成脚本
根据您的设置的介绍页面可以显示或不
选择“选择特定数据库对象”，
展开树视图，并检查相关表格
点击下一步
单击高级
在常规部分，选择“数据类型脚本”
相应的选项完成向导

然后，您将得到所有的数据的INSERT语句直接出SSMS。

编辑2016-10-25 SQL Server 2016/SSMS 13.0.15900。1

右击数据库名称
选择任务>生成脚本
根据您的设置的介绍页面可以显示或不
选择“选择特定数据库对象，
展开树视图和c赫克相关表格
单击下一步
单击高级
在常规部分，选择
单击OK '数据脚本的类型' 中选择相应的选项
选择是否要输出去一个新的查询，剪贴板或文件
单击下一步两次
你的剧本是根据你上面
挑设置准备单击完成

答

我使用SSMS 2008版10.0.5500.0。在这个版本中，作为生成脚本向导的一部分，而不是高级按钮，下面是一个屏幕。在这种情况下，我只想插入数据并且没有创建语句，所以我必须更改两个圈出来的属性 Script Options

答

我对这个问题的贡献是Powershell INSERT脚本生成器，它允许您脚本多个表而不必使用麻烦的SSMS GUI。非常适合将“种子”数据快速持久保存到源代码控制中。

将下面的脚本保存为“filename.ps1”。
对“CUSTOMIZE ME”下的区域进行自己的修改。
您可以将表格列表以任意顺序添加到脚本中。
你可以打开PowerShell ISE中的脚本，然后按下播放按钮，或者干脆在PowerShell命令提示符下执行脚本。

默认情况下，生成的INSERT脚本将与脚本位于同一文件夹下的“SeedData.sql”。

您将需要SQL Server管理对象安装组件，它应该有，如果你有安装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()

答

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

INSERT statement(s) generator

只是作为一个简单的和简单的例子，生成INSERT语句为表AdventureWorks.Person.AddressType执行以下语句：

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

，这将产生以下脚本：

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

答

这也可以使用Visual Studio（至少在2013版以后）完成。

在VS 2013中，也有可能来筛选插入语句所基于的行列表，这是SSMS中不可能的，就我所知。

执行以下步骤：

打开 “SQL Server的对象资源管理器” 窗口（菜单：/查看/ SQL Server的对象资源管理器）
打开/扩展数据库及其表
权点击表格并从上下文菜单中选择“查看数据”
这将显示主区域中的数据
可选步骤：点击过滤器图标“排序和过滤数据集”（第四个图标从在结果上方的行左边），并将一些过滤器应用于一列或多列
单击“脚本”或“脚本到文件”图标（顶部行右侧的图标，它们看起来像小片）

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

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

答

GenerateData是这个惊人的工具。因为源代码可供您使用，因此也很容易对其进行调整。有几个不错的功能：为人民名字

名称生成并放置
能够保存生成配置文件（这是下载并为本地后）
能力通过脚本来定制和操纵产生
数据的许多不同输出（CSV，Javascript，JSON等）（如果您需要在不同的环境中测试集并且想跳过数据库访问）
免费。但考虑捐赠，如果你发现该软件有用:)。

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

相关推荐