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

时间:2020-03-17 14:02来源:计算机教程
无意中看到朋友写的一篇文章“将表里的数据批量生成INSERT语句的存储过程的实现”。我仔细看文中的两个存储代码,自我感觉两个都不太满意,都是生成的单行模式的插入,数据行稍

无意中看到朋友写的一篇文章“将表里的数据批量生成INSERT语句的存储过程的实现”。我仔细看文中的两个存储代码,自我感觉两个都不太满意,都是生成的单行模式的插入,数据行稍微大些性能会受影响的。所在公司本来就存在第二个版本的类似实现,但是是基于多行模式的,还是需要手工添加UNAION ALL来满足多行模式的插入。看到这篇博文和基于公司数据行批量脚本的存储的缺点,这次改写和增强该存储的功能。

vnsc5858威尼斯城官网,本存储运行于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)和货币数据类型*/ 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 Server中数据行批量插入脚本的存储实现

关键词: