日本oricon,爽歪歪多少钱一箱,蓄财网
无意中看到朋友写的一篇文章“将表里的数据批量生成insert语句的存储过程的实现”。我仔细看文中的两个存储代码，自我感觉两个都不太满意，都是生成的单行模式的插入，数据行稍微大些性能会受影响的。所在公司本来就存在第二个版本的类似实现，但是是基于多行模式的，还是需要手工添加unaion all来满足多行模式的插入。看到这篇博文和基于公司数据行批量脚本的存储的缺点，这次改写和增强该存储的功能。
本存储运行于sql server 2005或以上版本，t-sql代码如下：
我要评论
if object_id(n'dbo.usp_getinsertsql', 'p') is not null
begin
drop procedure dbo.usp_getinsertsql;
end
go
--==================================
-- 功能: 获取数据表记录插入的sql脚本
-- 说明: 具体实现阐述
-- 作者: xxx
-- 创建: yyyy-mm-dd
-- 修改: yyyy-mm-dd xxx 修改内容描述
--==================================
create procedure dbo.usp_getinsertsql
(
@chvntable nvarchar(), -- 数据表名称(建议只使用表名称，不要带有分隔符[])
@chvnwhere nvarchar() = n'', -- where查询条件(不带where关键字）
@bitissinglerow bit = -- 是否单行模式，默认为单行模式(单行模式为单行insert into values格式；非单行模式（多行模式）为多行insert into select格式)
)
--$encode$--
as
begin
set nocount on;
set @bitissinglerow = isnull(@bitissinglerow, );
declare
@inttableid as int,
@chvnschematablename nvarchar();/*格式：[schema].[table]--++++++（各部分对应字符数）*/
select
@inttableid = ,
@chvnschematablename = n'';
select
@inttableid = object_id
,@chvnschematablename = quotename(schema_name(schema_id)) + n'.' + quotename(@chvntable) /*组合架构名称和表名称的连接*/
from sys.objects
where name = @chvntable
and type = 'u';
declare
@chvncolumnnames nvarchar(), -- 字段列名集，多个以逗号','分隔，格式如：[column_name],[column_name],...
@chvncolumnvalues as nvarchar(max); -- 字段列值集，多个以逗号','分隔
declare
@chvntsql as nvarchar(max), -- tsql脚本变量
@chvninsertintoboday as nvarchar(); -- insertinto主体变量
select
@chvntsql = n'',
@chvninsertintoboday = n'';
select
@chvncolumnnames = isnull(@chvncolumnnames + n',', n'') + quotename(t.column_name)
,@chvncolumnvalues = isnull(@chvncolumnvalues + n' + '','' + ', n'') + cast(t.column_value as nvarchar())
from (select
name as column_name /*字段列名*/
/*字段列值*/
,column_value = case
when system_type_id in (, , , , , , , , , , ) /*数字数据类型：整数数据类型(bit、tinyint、smallint、int、bigint)，带精度和小数的数据类型(decimal、numeric)和货币数据类型（monery和smallmoney*/
then 'case when '+ name + ' is null then ''null'' else cast(' + name + ' as varchar) end'
when system_type_id in (, , , , ) /*日期和时间数据类型：datetime、smalldatetime（兼容sql server 新增 date、datetime和time）*/
then 'case when '+ name + ' is null then ''null'' else '''''''' + replace(convert(varchar(), ' + name + ', ), '' ::.'', '''') + '''''''' end'
when system_type_id in () /*字符串数据类型：varchar*/
then 'case when '+ name + ' is null then ''null'' else '''''''' + replace(' + name + ', '''''''', '''''''''''') + '''''''' end'
when system_type_id in () /*unicode字符串数据类型：nvarchar*/
then 'case when '+ name + ' is null then ''null'' else ''n'''''' + replace(' + name + ', '''''''','''''''''''') + '''''''' end'
when system_type_id in () /*字符串数据类型：char*/
then 'case when '+ name + ' is null then ''null'' else '''''''' + cast(replace(' + name + ', '''''''' ,'''''''''''') as char(' + cast(max_length as varchar) + ')) + '''''''' end'
when system_type_id in () /*nicode字符串数据类型：nchar*/
then 'case when '+ name + ' is null then ''null'' else ''n'''''' + cast(replace(' + name + ', '''''''' ,'''''''''''') as char(' + cast(max_length as varchar) + ')) + '''''''' end'
else '''null''' end
from sys.columns
where object_id = @inttableid
) as t;
set @chvninsertintoboday = n'''insert into '+ @chvnschematablename + n' (' + @chvncolumnnames + n')''';
-- 方式一、代码格式使用了goto和label
--begin
-- if @bitissinglerow = /*多行模式*/
-- begin
-- set @chvntsql = n'select ''select '' + ' + @chvncolumnvalues + ' as rowdata, row_number() over(order by (select null)) as rownum from ' + @chvnschematablename
-- -- 此处不能使用goto wherecondition;,因为之后的代码不会被执行
-- if @chvnwhere > ''
-- begin
-- set @chvntsql = @chvntsql + ' where ' + @chvnwhere;
-- end
-- -- 处理多行模式，需要使用row_number窗口函数
-- set @chvntsql = n'select case when t.rownum = then replicate(n'' '', len(n''union all '') + ) + t.rowdata else n''union all '' + t.rowdata end' +
-- n' from (' + @chvntsql + n') as t';
-- set @chvntsql = n'select '+ @chvninsertintoboday + n';' +
-- @chvntsql;
-- goto multirow;
-- end
-- else if @bitissinglerow = /*当行模式*/
-- begin
-- set @chvntsql = n'select ' + @chvninsertintoboday +
-- n' + ''values('' + ' + @chvncolumnvalues + ' + '');'' from ' + @chvnschematablename;
-- goto wherecondition;
-- end
-- -- where查询条件
-- wherecondition:
-- if @chvnwhere > ''
-- begin
-- set @chvntsql = @chvntsql + ' where ' + @chvnwhere;
-- end
-- multirow:/*多行模式goto的label空标记*/
--end
-- 方式二、存在部分代码的冗余
begin
if @bitissinglerow = /*多行模式*/
begin
set @chvntsql = n'select ''select '' + ' + @chvncolumnvalues + ' as rowdata, row_number() over(order by (select null)) as rownum from ' + @chvnschematablename
if @chvnwhere > ''
begin
set @chvntsql = @chvntsql + ' where ' + @chvnwhere;
end
-- 多行模式特殊代码，需要使用row_number窗口函数
set @chvntsql = n'select case when t.rownum = then replicate(n'' '', len(n''union all '') + ) + t.rowdata else n''union all '' + t.rowdata end' +
n' from (' + @chvntsql + n') as t';
set @chvntsql = n'select '+ @chvninsertintoboday + n';' +
@chvntsql;
end
else if @bitissinglerow = /*单行模式*/
begin
set @chvntsql = n'select ' + @chvninsertintoboday +
n' + ''values('' + ' + @chvncolumnvalues + ' + '');'' from ' + @chvnschematablename;
if @chvnwhere > ''
begin
set @chvntsql = @chvntsql + ' where ' + @chvnwhere;
end
end
end
print @chvntsql;
exec(@chvntsql);
end
go
为了测试以上存储的效果，下面准备一个有数据的数据表，t-sql代码如下：
if object_id(n'dbo.userlogininfo', n'u') is not null
begin
drop table dbo.userlogininfo;
end
go
-- create testing table userlogininfo
create table dbo.userlogininfo (
id int identity(, ) primary key,
name varchar() not null,
logintime datetime not null
);
go
-- insert testing data
insert dbo.userlogininfo (name, logintime) values
('zhang', '-- ::')
,('li', '-- ::')
,('wang', '-- ::')
,('zhang', '-- ::')
,('li', '-- ::')
,('wang', '-- ::')
,('zhang', '-- ::')
,('li', '-- ::')
,('wang', '-- ::')
,('zhang', '-- ::')
,('li', '-- ::')
,('wang', '-- ::')
,('zhang', '-- ::')
,('li', '-- ::')
,('li', '-- ::')
,('li', '-- ::')
,('li', '-- ::')
,('li', '-- ::')
,('li', '-- ::')
,('li', '-- ::')
,('li', '-- ::')
,('wang', '-- ::')
,('zhang', '-- ::')
,('li', '-- ::')
,('wang', '-- ::')
,('zhang', '-- ::')
,('li', '-- ::')
,('wang', '-- ::')
,('zhang', '-- ::')
,('li', '-- ::')
,('wang', '-- ::');
go
先测试单行模式的效果，相应的t-sql代码如下：
exec dbo.usp_getinsertsql
@chvntable = n'userlogininfo', -- nvarchar()
@chvnwhere = n'', -- nvarchar()
@bitissinglerow = ; -- bit
go
执行后的查询结果如下：
再测试多行模式的效果，相应的t-sql代码如下：
exec dbo.usp_getinsertsql @chvntable = n'userlogininfo', -- nvarchar() @chvnwhere = n'', -- nvarchar() @bitissinglerow = ; -- bit go
执行后的查询效果如下：
注意：多行模式，还需要将以上的两个结果前后合并在一个文件就可以啦。
以上内容是小编给大家分享的sql server中数据行批量插入脚本的存储实现，希望大家喜欢。
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论