香港云主机最佳企业级服务商!

ADSL拨号VPS包含了中国大陆(联通,移动,电信,)

中国香港,国外拨号VPS。

当前位置:云主机 > MSSQL >

电信ADSL拨号VPS
联通ADSL拨号VPS
移动ADSL拨号VPS

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


时间:2020-10-31 14:29 作者:admin610456


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

(责任编辑:admin)






帮助中心
会员注册
找回密码
新闻中心
快捷通道
域名登录面板
虚机登录面板
云主机登录面板
关于我们
关于我们
联系我们
联系方式

售前咨询:17830004266(重庆移动)

企业QQ:383546523

《中华人民共和国工业和信息化部》 编号:ICP备00012341号

Copyright © 2002 -2018 香港云主机 版权所有
声明:香港云主机品牌标志、品牌吉祥物均已注册商标,版权所有,窃用必究

云官方微信

在线客服

  • 企业QQ: 点击这里给我发消息
  • 技术支持:383546523

  • 公司总台电话:17830004266(重庆移动)
  • 售前咨询热线:17830004266(重庆移动)