从数据库中获取Insert语句

经常需要从开发库中提交生成数据的sql到测试库中。之前采用的方式：

1、自己写sql，将sql保存起来，等测试的时候提交这些sql。

2、在ERP系统中添加数据使用SQL SERVER Profiler跟踪SQL，保存起来，等测试的时候提交这些sql。

两种方式存在的问题：

1、如果修改一些数据，必须将更新语句也保存起来。

2、保存的sql如果没有很好的管理机制，容易丢失。

所以，在网上找了一下，写了一个根据数据库表名称和过滤条件生成insert的语句的存储过程，当提交测试的时候根据这个存储过程产生insert语句就行了。主要代码来自：Generating INSERT statements in SQL Server。我根据业务需求做了相应的调整，代码如下：

使用这个存储过程：

SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO DROP PROC InsertGenerator go CREATE PROC InsertGenerator ( @tableName VARCHAR(100) , @filterCondition VARCHAR(100) ) AS --定义一个游标获取数据表列的相关信息 DECLARE cursCol CURSOR FAST_FORWARD FOR SELECT column_name , data_type FROM information_schema.columns WHERE table_name = @tableName OPEN cursCol DECLARE @string NVARCHAR(3000) --存放insert语句的前一半 DECLARE @stringData NVARCHAR(3000) --存放 (VALUES) DECLARE @dataType NVARCHAR(1000) --列的数据类型 SET @string = 'INSERT ' + @tableName + '(' SET @stringData = '' DECLARE @colName NVARCHAR(50) FETCH NEXT FROM cursCol INTO @colName, @dataType IF @@fetch_status <> 0 BEGIN PRINT 'Table ' + @tableName + ' not found, processing skipped.' CLOSE curscol DEALLOCATE curscol RETURN END WHILE @@FETCH_STATUS = 0 BEGIN IF @dataType IN ( 'varchar', 'char', 'nchar', 'nvarchar' ) BEGIN SET @stringData = @stringData + '''' + '''+isnull(''''' + '''''+' + @colName + '+''''' + ''''',''NULL'')+'',''+' END ELSE IF @dataType IN ( 'text', 'ntext' ) --text 类型 BEGIN SET @stringData = @stringData + '''''''''+isnull(cast(' + @colName + ' as varchar(2000)),'''')+'''''',''+' END ELSE IF @dataType = 'money' -- money 类型 BEGIN SET @stringData = @stringData + '''convert(money,''''''+isnull(cast(' + @colName + ' as varchar(200)),''0.0000'')+''''''),''+' END ELSE IF @dataType = 'datetime'-- datetime 类型 BEGIN SET @stringData = @stringData + '''convert(datetime,' + '''+isnull(''''' + '''''+convert(varchar(200),' + @colName + ',121)+''''' + ''''',''NULL'')+'',121),''+' END ELSE IF @dataType = 'image' -- image 类型 BEGIN SET @stringData = @stringData + '''''''''+isnull(cast(convert(varbinary,' + @colName + ') as varchar(6)),''0'')+'''''',''+' END ELSE -- int,bit,numeric,decimal 类型 BEGIN SET @stringData = @stringData + '''' + '''+isnull(''''' + '''''+convert(varchar(200),' + @colName + ')+''''' + ''''',''NULL'')+'',''+' END SET @string = @string + @colName + ',' FETCH NEXT FROM cursCol INTO @colName, @dataType END DECLARE @Query NVARCHAR(4000) --设置表的过滤条件 IF @filterCondition = '' OR @filterCondition IS NULL BEGIN SET @filterCondition = ' 1=1 ' END SET @query = 'SELECT ''' + SUBSTRING(@string, 0, LEN(@string)) + ') VALUES(''+ ' + SUBSTRING(@stringData, 0, LEN(@stringData) - 2) + '''+'')'' FROM ' + @tableName + ' where ' + @filterCondition PRINT @query EXEC sp_executesql @query CLOSE cursCol DEALLOCATE cursCol GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

1、不使用过滤条件功能

InsertGenerator leave,''

得到生成insert语句的sql为：

SELECT 'INSERT leave(LeaveGUID,LeaveName,LeaveDay,Memo,Status) VALUES(' + '' + ISNULL('''' + CONVERT(VARCHAR(200), LeaveGUID) + '''', 'NULL') + ',' + '' + ISNULL('''' + LeaveName + '''', 'NULL') + ',' + '' + ISNULL('''' + CONVERT(VARCHAR(200), LeaveDay) + '''', 'NULL') + ',' + '' + ISNULL('''' + Memo + '''', 'NULL') + ',' + '' + ISNULL('''' + Status + '''', 'NULL') + '' + ')' FROM leave WHERE 1 = 1

结果：

2、使用过滤添加条件功能：

InsertGenerator leave,'status=''同意'''

得到生成insert语句的sql为：

SELECT 'INSERT leave(LeaveGUID,LeaveName,LeaveDay,Memo,Status) VALUES(' + '' + ISNULL('''' + CONVERT(VARCHAR(200), LeaveGUID) + '''', 'NULL') + ',' + '' + ISNULL('''' + LeaveName + '''', 'NULL') + ',' + '' + ISNULL('''' + CONVERT(VARCHAR(200), LeaveDay) + '''', 'NULL') + ',' + '' + ISNULL('''' + Memo + '''', 'NULL') + ',' + '' + ISNULL('''' + Status + '''', 'NULL') + '' + ')' FROM leave WHERE status = '同意'

结果：