代码之家 › 专栏 › 技术社区 › JosephStyons

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

code-generation ssms sql-server

519

JosephStyons · 技术社区 · 15 年前

我们正在编写一个新的应用程序,在测试时,我们需要一组虚拟数据。我已经通过使用MS Access将Excel文件转储到相关表中添加了这些数据。

通常,我们希望“刷新”相关表,这意味着删除所有表,重新创建它们,并运行保存的MS Access追加查询。

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

我现在把数据放在表格里了。从该数据集中自动生成一个大的insert语句列表的最佳方法是什么?

我唯一能想到的方法是将表保存到Excel工作表中,然后编写Excel公式为每行创建一个插入,这肯定不是最好的方法。

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

19 回复 | 直到 6 年前

900

David Ferenczy RogoÅ¾an Hugo L.M 8 年前

微软应该宣传SSMS 2008的这种功能。您要查找的功能内置在 生成脚本 实用程序,但该功能在默认情况下是关闭的,并且在编写表脚本时必须启用。

这是一个快速运行以生成 INSERT 表中所有数据的语句,不使用SQL Management Studio 2008的脚本或外接程序:

右键单击数据库并转到任务 gt; 生成脚本 .
选择要针对其生成脚本的表(或对象)。
去 设置脚本选项 选项卡并单击 先进的 按钮。
在一般类别,转到 要编写脚本的数据类型
有3个选项: 仅图式 , 仅数据 和 模式与数据 . 选择适当的选项并单击好啊 .

然后你会得到 CREATE TABLE 声明和所有 插入 直接从SSMS获取数据的语句。

einpoklum 6 年前

我们使用这个存储过程-它允许您以特定的表为目标,并使用WHERE子句。你可以找到文本 here .

例如,它允许您这样做:

EXEC sp_generate_inserts 'titles'

noonand 8 年前

正如@Mike Ritacco提到的,但针对SSMS 2008 R2进行了更新。

右键单击数据库名称
选择任务>生成脚本
根据您的设置,简介页可能会显示或不显示
选择“选择特定数据库对象”,
展开树视图并检查相关表
单击下一步
单击高级
在“常规”部分下,为“要编写脚本的数据类型”选择适当的选项。
完成向导

然后,您将直接从ssms中获取数据的所有insert语句。

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

右键单击数据库名称
选择任务>生成脚本
根据您的设置,简介页可能会显示或不显示
选择“选择特定数据库对象”,
展开树视图并检查相关表
单击下一步
单击高级
在“常规”部分下,为“数据类型”选择适当的选项脚本
单击确定
选择是否希望输出转到新查询、剪贴板或文件
单击下一步两次
您的脚本是根据您在上面选择的设置准备的
单击完成

Martin 8 年前

这可以用 Visual Studio 同样(至少在2013年以后的版本中)。

与2013年相比,这也是可能的过滤 inserts语句所基于的行列表,据我所知,这在ssms中是不可能的。

执行以下步骤:

打开“SQL Server对象资源管理器”窗口(菜单:/View/SQL Server对象资源管理器)
打开/展开数据库及其表
右键单击表并从上下文菜单中选择“查看数据”
这将显示主区域中的数据
可选步骤:单击筛选图标“排序和筛选数据集”(结果上方行左侧的第四个图标),并对一列或多列应用一些筛选
单击“脚本”或“脚本到文件”图标(最上面一行右侧的图标,它们看起来像小纸片)

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

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

BinaryHacker 15 年前

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

http://www.ssmstoolspack.com/

Erikest 13 年前

我使用的是SSMS 2008版本10.0.5500.0。在此版本中,作为生成脚本向导的一部分,下面是屏幕,而不是高级按钮。在本例中,我只需要插入数据,而不需要创建语句,因此必须更改两个带圆圈的属性 Script Options

harriyott Erik Funkenbusch 12 年前

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

marc_s 13 年前

第一个指向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 
...

charlie 9 年前

我对这个问题的贡献是,一个PowerShell插入脚本生成器,它允许您编写多个表的脚本,而不必使用繁琐的SSMS GUI。非常适合快速将“种子”数据保存到源代码管理中。

将下面的脚本另存为“filename.ps1”。
对“自定义我”下的区域进行自己的修改。
可以按任意顺序将表列表添加到脚本中。
您可以在PowerShell ISE中打开该脚本并单击“播放”按钮,或者只需在PowerShell命令提示符中执行该脚本即可。

默认情况下,生成的插入脚本将与脚本位于同一文件夹下的“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()

drumsta 9 年前

如果需要编程访问,则可以使用开放源代码存储过程'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

janem 15 年前

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

它有一个向导帮助您编写INSERT语句脚本。

ShuggyCoUk 15 年前

不使用插入,使用 BCP

Crowie Ralph 10 年前

我用过这个 script 我把它放在我的博客上了( 如何在SQL Server上生成INSERT语句过程 )

到目前为止对我有效,尽管它们可能是我还没有发现的虫子。

Klik 8 年前

GenerateData 这是一个了不起的工具。也很容易对其进行调整,因为源代码对您是可用的。一些不错的功能:

人名和地名生成器
保存生成配置文件的能力(下载并在本地设置后)
能够通过脚本自定义和操作生成
数据的许多不同输出(csv、javascript、json等)(如果您需要在不同的环境中测试集合并希望跳过数据库访问)
免费的 . 但是如果你觉得软件有用的话,可以考虑捐赠。

Paul Harrington 15 年前

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

sqlite3 foo.sqlite .dump > foo_as_a_bunch_of_inserts.sql

Nick DeVore 15 年前

生产数据库中是否有数据?如果是这样,您可以通过DTS设置数据的周期刷新。我们在周末每周都做一次测试,每周都有干净、真实的数据供我们测试是非常好的。

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

shahkalpesh 15 年前

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

如果您有MS Access中的数据,并且希望将其移动到SQL Server,那么可以使用DTS。
而且,我想您可以使用SQL事件探查器来查看所有的insert语句。

Vineet Neema 15 年前

对此我也做了大量的研究,但我无法找到具体的解决办法。目前我采用的方法是从SQL Server Management Studio复制Excel中的内容,然后将数据导入Oracle Toad,然后生成insert语句。

-1

KM. 15 年前

为什么不在使用数据之前备份数据,然后在需要刷新数据时进行恢复呢?

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