经常需要从开发库中提交生成数据的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 <>
begin
print ' Table ' + @tableName + ' not found, processing skipped. '
close curscol
deallocate curscol
return
END
WHILE @@FETCH_STATUS =
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)), '' '' )+ '''''' , '' + '
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, , len( @string)) + ' ) VALUES( '' + ' + substring( @stringData, , 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
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 <>
begin
print ' Table ' + @tableName + ' not found, processing skipped. '
close curscol
deallocate curscol
return
END
WHILE @@FETCH_STATUS =
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)), '' '' )+ '''''' , '' + '
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, , len( @string)) + ' ) VALUES( '' + ' + substring( @stringData, , 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
+ 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 = ' 同意 '
+ 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 = ' 同意 '
结果:略
原文链接: http://www.cnblogs.com/zhuqil/archive/2011/09/22/2184904.html