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

站长资源 2024/12/26 佚名

67 1538 67

DDR爱好者之家 Design By 杰米

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

sqlserver批量插入脚本,存储过程

DDR爱好者之家 Design By 杰米

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

DDR爱好者之家 Design By 杰米

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

暂无评论...

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

将表里的数据批量生成INSERT语句的存储过程增强版

SQL Server FileStream详解

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

P70系列延期，华为新旗舰将在下月发布

更新日志

友情链接

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

将表里的数据批量生成INSERT语句的存储过程 增强版

SQL Server FileStream详解

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

P70系列延期，华为新旗舰将在下月发布

更新日志

友情链接

将表里的数据批量生成INSERT语句的存储过程增强版