经常需要从开发库中提交生成数据的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 = '同意'
结果: