我们正在编写一个新的应用程序，在进行测试时，我们将需要一堆虚拟数据。我已经通过使用MS Access将excel文件转储到相关表中添加了这些数据。
每隔一段时间，我们想要“刷新”相关的表，这意味着将它们全部删除，重新创建它们，并运行已保存的MS Access追加查询。
第一部分(删除和重新创建)是一个简单的sql脚本，但最后一部分让我畏缩。我想要一个单独的设置脚本，它有一堆insert来重新生成虚拟数据。
我现在有数据在表格里。从该数据集自动生成一个大的INSERT语句列表的最佳方法是什么?
我能想到的唯一方法是将表保存到excel表格中，然后编写excel公式为每一行创建一个INSERT，这肯定不是最好的方法。
我使用2008 Management Studio连接到SQL Server 2005数据库。
当前回答
我对这个问题的贡献是一个Powershell INSERT脚本生成器，它允许您为多个表编写脚本，而不必使用麻烦的SSMS GUI。非常适合快速将“种子”数据持久化到源代码控制中。
将下面的脚本保存为“filename.ps1”。 对“自定义我”下的区域进行自己的修改。 您可以以任意顺序将表列表添加到脚本中。 您可以在Powershell ISE中打开脚本并点击播放按钮，或者简单地在Powershell命令提示符中执行脚本。
默认情况下，生成的INSERT脚本将是“SeedData”。与脚本相同的文件夹下。
您需要安装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()
其他回答
GenerateData是一个很棒的工具。对它进行调整也很容易，因为源代码是可用的。一些不错的功能:
用于人名和地名的名称生成器 能够保存生成配置文件(在本地下载和设置后) 能够通过脚本自定义和操作生成 数据的许多不同输出(CSV, Javascript, JSON等)(以防您需要在不同的环境中测试集，并希望跳过数据库访问) 免费的。但如果你觉得软件有用，可以考虑捐赠:)。
我做了一个简单易用的实用程序，希望你喜欢。
它不需要在数据库上创建任何对象(易于在生产环境中使用)。 你不需要安装任何东西。这只是一个普通的脚本。 您不需要特殊权限。常规的读访问就足够了。 允许您复制表的所有行，或指定WHERE条件，以便只生成您想要的行。 允许您指定要生成的单个或多个表和不同的条件语句。
如果生成的INSERT语句被截断，在Management Studio Options: Tools > Options, Query results > SQL Server > results to Grid，“Non XML data”value下的“Maximum Characters retrieve”中检查结果的限制文本长度。
-- Make sure you're on the correct database
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @Tables TABLE (
TableName varchar(50) NOT NULL,
Arguments varchar(1000) NULL
);
-- INSERT HERE THE TABLES AND CONDITIONS YOU WANT TO GENERATE THE INSERT STATEMENTS
INSERT INTO @Tables (TableName, Arguments) VALUES ('table1', 'WHERE field1 = 3101928464');
-- (ADD MORE LINES IF YOU LIKE) INSERT INTO @Tables (TableName, Arguments) VALUES ('table2', 'WHERE field2 IN (1, 3, 5)');
-- YOU DON'T NEED TO EDIT FROM NOW ON.
-- Generating the Script
DECLARE @TableName varchar(50),
@Arguments varchar(1000),
@ColumnName varchar(50),
@strSQL varchar(max),
@strSQL2 varchar(max),
@Lap int,
@Iden int,
@TypeOfData int;
DECLARE C1 CURSOR FOR
SELECT TableName, Arguments FROM @Tables
OPEN C1
FETCH NEXT FROM C1 INTO @TableName, @Arguments;
WHILE @@FETCH_STATUS = 0
BEGIN
-- If you want to delete the lines before inserting, uncomment the next line
-- PRINT 'DELETE FROM ' + @TableName + ' ' + @Arguments
SET @strSQL = 'INSERT INTO ' + @TableName + ' (';
-- List all the columns from the table (to the INSERT into columns...)
SET @Lap = 0;
DECLARE C2 CURSOR FOR
SELECT sc.name, sc.type FROM syscolumns sc INNER JOIN sysobjects so ON so.id = sc.id AND so.name = @TableName AND so.type = 'U' WHERE sc.colstat = 0 ORDER BY sc.colorder
OPEN C2
FETCH NEXT FROM C2 INTO @ColumnName, @TypeOfData;
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@Lap>0)
BEGIN
SET @strSQL = @strSQL + ', ';
END
SET @strSQL = @strSQL + '[' + @ColumnName + ']';
SET @Lap = @Lap + 1;
FETCH NEXT FROM C2 INTO @ColumnName, @TypeOfData;
END
CLOSE C2
DEALLOCATE C2
SET @strSQL = @strSQL + ')'
SET @strSQL2 = 'SELECT ''' + @strSQL + '
SELECT '' + ';
-- List all the columns from the table again (for the SELECT that will be the input to the INSERT INTO statement)
SET @Lap = 0;
DECLARE C2 CURSOR FOR
SELECT sc.name, sc.type FROM syscolumns sc INNER JOIN sysobjects so ON so.id = sc.id AND so.name = @TableName AND so.type = 'U' WHERE sc.colstat = 0 ORDER BY sc.colorder
OPEN C2
FETCH NEXT FROM C2 INTO @ColumnName, @TypeOfData;
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@Lap>0)
BEGIN
SET @strSQL2 = @strSQL2 + ' + '', '' + ';
END
-- For each data type, convert the data properly
IF(@TypeOfData IN (55, 106, 56, 108, 63, 38, 109, 50, 48, 52)) -- Numbers
SET @strSQL2 = @strSQL2 + 'ISNULL(CONVERT(varchar(max), ' + @ColumnName + '), ''NULL'') + '' as ' + @ColumnName + '''';
ELSE IF(@TypeOfData IN (60, 62)) -- Float Numbers
SET @strSQL2 = @strSQL2 + 'ISNULL(CONVERT(varchar(max), CONVERT(decimal(18,5), ' + @ColumnName + ')), ''NULL'') + '' as ' + @ColumnName + '''';
ELSE IF(@TypeOfData IN (61, 111)) -- Datetime
SET @strSQL2 = @strSQL2 + 'ISNULL( '''''''' + CONVERT(varchar(max),' + @ColumnName + ', 121) + '''''''', ''NULL'') + '' as ' + @ColumnName + '''';
ELSE IF(@TypeOfData IN (37, 47, 39, 0, 110)) -- Texts
SET @strSQL2 = @strSQL2 + 'ISNULL('''''''' + RTRIM(LTRIM(' + @ColumnName + ')) + '''''''', ''NULL'') + '' as ' + @ColumnName + '''';
ELSE -- Unknown data types
SET @strSQL2 = @strSQL2 + 'ISNULL(CONVERT(varchar(max), ' + @ColumnName + '), ''NULL'') + '' as ' + @ColumnName + '(INCORRECT TYPE ' + CONVERT(varchar(10), @TypeOfData) + ')''';
SET @Lap = @Lap + 1;
FETCH NEXT FROM C2 INTO @ColumnName, @TypeOfData;
END
CLOSE C2
DEALLOCATE C2
SET @strSQL2 = @strSQL2 + ' as [-- ' + @TableName + ']
FROM ' + @TableName + ' WITH (NOLOCK) ' + @Arguments
SET @strSQL2 = @strSQL2 + ';
';
--PRINT @strSQL;
--PRINT @strSQL2;
EXEC(@strSQL2);
FETCH NEXT FROM C1 INTO @TableName, @Arguments;
END
CLOSE C1
DEALLOCATE C1
ROLLBACK
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT 0 AS Situacao;
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage,
@strSQL As strSQL,
@strSQL2 as strSQL2;
END CATCH
不要使用插入，使用BCP
微软应该宣传SSMS 2008的这个功能。您正在寻找的特性内置于Generate Script实用程序中，但是该功能在默认情况下是关闭的，并且在编写表脚本时必须启用。
这是一个快速运行，为您表中的所有数据生成INSERT语句，不使用SQL Management Studio 2008脚本或插件:
右键单击数据库，转到Tasks > Generate Scripts。 选择要针对其生成脚本的表(或对象)。 转到设置脚本选项选项卡，然后单击高级按钮。 在“常规”类别中，转到“脚本的数据类型” 有3个选项:仅模式、仅数据和模式和数据。选择适当的选项并单击OK。
然后，您将直接从SSMS中获取数据的CREATE TABLE语句和所有INSERT语句。
正如@Mike Ritacco提到的，但已为SSMS 2008 R2更新
右键单击数据库名称 选择任务>生成脚本 根据您的设置，介绍页面可能会显示或不显示 选择“选择特定的数据库对象”， 展开树视图并检查相关表 单击Next 单击高级 在“常规”部分，为“要脚本的数据类型”选择适当的选项 完成向导
然后，您将直接从SSMS中获取数据的所有INSERT语句。
SQL Server 2016/SSMS 13.0.159001
Right click on the database name Choose Tasks > Generate scripts Depending on your settings the intro page may show or not Choose 'Select specific database objects', Expand the tree view and check the relevant tables Click Next Click Advanced Under General section, choose the appropriate option for 'Types of data to script' Click OK Pick whether you want the output to go to a new query, the clipboard or a file Click Next twice Your script is prepared in accordance with the settings you picked above Click Finish
