从数据库中获取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 = '同意'
复制代码

结果:略




本文转自麒麟博客园博客,原文链接:http://www.cnblogs.com/zhuqil/archive/2011/09/22/2184904.html,如需转载请自行联系原作者

相关文章
|
2月前
|
数据库 OceanBase 索引
在OceanBase数据库中,REPLACE INTO和insert update在效率上可能有所不同
【2月更文挑战第30天】在OceanBase数据库中,REPLACE INTO和insert update在效率上可能有所不同
56 1
|
Oracle 关系型数据库 数据库
Oracle生产数据库insert插入较慢分析过程和解决办法
Oracle生产数据库insert插入较慢分析过程和解决办法
338 0
|
10月前
|
数据库
delphi insert各种类型数据(数据库InterBase)
delphi insert各种类型数据(数据库InterBase)
|
12月前
|
SQL 缓存 数据管理
开源分布式数据库PolarDB-X源码解读——PolarDB-X源码解读(五):DML之Insert流程.
开源分布式数据库PolarDB-X源码解读——PolarDB-X源码解读(五):DML之Insert流程.
628 0
|
SQL 数据库 Python
Python 数据库Insert语句脚本生成工具(SQL Server)
Python 数据库Insert语句脚本生成工具(SQL Server)
317 0
Python 数据库Insert语句脚本生成工具(SQL Server)
|
Java 数据库连接 数据库
使用JDBC(Dbutils工具包)来从数据库拿取map类型数据来动态生成insert语句
前言: 大家在使用JDBC来连接数据库时,我们通过Dbutils工具来拿取数据库中的数据,可以使用new BeanListHandler<>(所映射的实体类.class),这样得到的数据,不知道表的字段名字,我们在往数据库里添加时,需要自己来挨个写字段,非常麻烦! 于是,小编想到通过MapListHandler(),结果集为一个List<Map<String, Object>>,map中key为数据库字段名字,value为对应的值,这样就可以实现insert语句动态拼接了!!
169 3
|
存储 SQL 架构师
频繁插入(insert)的业务,用什么存储引擎更合适? | 数据库系列
虽然MyISAM只支持表锁,但高并发select与insert的业务场景,3个知识点告诉你,MyISAM的表锁依然有非常强劲的性能。
956 0
频繁插入(insert)的业务,用什么存储引擎更合适? | 数据库系列
|
关系型数据库 MySQL 数据库
mysql数据库使用insert语句插入中文数据报错
mysql数据库使用insert语句插入中文数据报错在mysql的命令行模式中,通过insert语句插入中文数据的时候报错,类似于下面这样: Incorrect string value: 'xE7x8F' for column 'name' at row 1造成这个错误通常是由于创建数据表的时候使用了不正确的编码格式,可以使用如下命令查看操作的目标数据表的编码格式。
10874 0
|
关系型数据库 MySQL 数据库
mysql数据库使用insert语句插入中文数据报错
在mysql的命令行模式中,通过insert语句插入中文数据的时候报错,类似于下面这样: Incorrect string value: '\xE7\x8F' for column 'name' at row 1 造成这个错误通常是由于创建数据表的时候使用了不正确的编码格式,可以使用如下命令查看操作的目标数据表的编码格式。
3461 0
|
SQL Java 关系型数据库
Java jdbc连接数据库 INSERT插入
Java jdbc连接数据库 INSERT插入 package com.edu; import java.
3005 0