SQL Server上批量插入脚本用什么方法，SQL语句是什么

发布时间：2023-04-19 13:32:32 所属栏目：MsSql教程来源：

导读：这篇文章主要介绍“SQL Server中批量插入脚本用什么方法，SQL语句是什么”的相关知识，下面会通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“SQL Server中批量插入脚本用什

这篇文章主要介绍“SQL Server中批量插入脚本用什么方法，SQL语句是什么”的相关知识，下面会通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“SQL Server中批量插入脚本用什么方法，SQL语句是什么”文章能帮助大家解决问题。

无意中看到朋友写的一篇文章“将表里的数据批量生成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

注意：多行模式，还需要将以上的两个结果前后合并在一个文件就可以啦。

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!