SQL Server中数据行批量插入脚本的存储实现

无意中看到朋友写的一篇文章“将表里的数据批量生成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中数据行批量插入脚本的存储实现，希望大家喜欢。