你的位置:首页 > 数据库

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


    看到博友SQL Server MVP桦仔的一篇博文“将表里的数据批量生成INSERT语句的存储过程的实现”。我仔细看来博文中的两个存储代码,自我感觉两个都不太满意,都是生成的单行模式的插入,数据行稍微大些性能会受影响的。所在公司本来就存在第二个版本的类似实现,但是是基于多行模式的,还是需要手工添加UNAION ALL来满足多行模式的插入。看到这边博文和基于公司数据行批量脚本的存储的确定,这次改写和增强该存储的功能。
    本存储运行于SQL Server 2005或以上版本,T-SQL代码如下:
 1 IF OBJECT_ID(N'dbo.usp_GetInsertSQL', 'P') IS NOT NULL 2 BEGIN 3   DROP PROCEDURE dbo.usp_GetInsertSQL; 4 END 5 GO 6  7 --================================== 8 -- 功能: 获取数据表记录插入的SQL脚本 9 -- 说明: 具体实现阐述  10 -- 作者: XXX 11 -- 创建: yyyy-MM-dd 12 -- 修改: yyyy-MM-dd XXX 修改内容描述 13 --================================== 14 CREATE PROCEDURE dbo.usp_GetInsertSQL  15 ( 16   @chvnTable NVARCHAR(128),      -- 数据表名称(建议只使用表名称,不要带有分隔符[]) 17   @chvnWhere NVARCHAR(1000) = N'',  -- where查询条件(不带WHERE关键字) 18   @bitIsSingleRow BIT = 1        -- 是否单行模式,默认为单行模式(单行模式为单行INSERT INTO VALUES格式;非单行模式(多行模式)为多行INSERT INTO SELECT格式) 19 ) 20   --$Encode$-- 21 AS 22 BEGIN 23   SET NOCOUNT ON; 24  25   SET @bitIsSingleRow = ISNULL(@bitIsSingleRow, 1); 26  27   DECLARE 28     @intTableID AS INT, 29     @chvnSchemaTableName NVARCHAR(261);/*格式:[schema].[table]--1+128+1+1+1+128+1(各部分对应字符数)*/ 30  31   SELECT 32     @intTableID = 0, 33     @chvnSchemaTableName = N''; 34  35   SELECT 36     @intTableID = object_id 37     ,@chvnSchemaTableName = QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(@chvnTable) /*组合架构名称和表名称的连接*/ 38   FROM sys.objects 39   WHERE name = @chvnTable 40     AND type = 'U'; 41  42   DECLARE 43     @chvnColumnNames NVARCHAR(2000),        -- 字段列名集,多个以逗号','分隔,格式如:[column_name],[column_name],... 44     @chvnColumnValues AS NVARCHAR(MAX);        -- 字段列值集,多个以逗号','分隔   45  46   DECLARE  47     @chvnTSQL AS NVARCHAR(MAX),            -- TSQL脚本变量 48     @chvnInsertIntoBoday AS NVARCHAR(2100);      -- InsertInto主体变量 49   SELECT 50     @chvnTSQL = N'', 51     @chvnInsertIntoBoday = N''; 52  53   SELECT 54     @chvnColumnNames = ISNULL(@chvnColumnNames + N',', N'') + QUOTENAME(T.column_name) 55     ,@chvnColumnValues = ISNULL(@chvnColumnValues + N' + '','' + ', N'') + CAST(T.column_value AS NVARCHAR(800)) 56   FROM (SELECT  57         name AS column_name /*字段列名*/ 58         /*字段列值*/ 59         ,column_value = CASE 60           WHEN system_type_id IN (48, 52, 56, 59, 60, 62, 104, 106, 108, 122, 127) /*数字数据类型:整数数据类型(bit、tinyint、smallint、int、bigint),带精度和小数的数据类型(decimal、numeric)和货币数据类型(monery和smallmoney*/               61               THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE CAST(' + name + ' AS VARCHAR) END' 62           WHEN system_type_id IN (58, 61, 40, 41, 42) /*日期和时间数据类型:datetime、smalldatetime(兼容sql server 2008新增 date、datetime2和time)*/ 63               THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE '''''''' + REPLACE(CONVERT(VARCHAR(22), ' + name + ', 23), '' 00:00:00.000'', '''') + '''''''' END' 64           WHEN system_type_id IN (167) /*字符串数据类型:varchar*/ 65               THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE '''''''' + REPLACE(' + name + ', '''''''', '''''''''''') + '''''''' END' 66           WHEN system_type_id IN (231) /*Unicode字符串数据类型:nvarchar*/ 67               THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE ''N'''''' + REPLACE(' + name + ', '''''''','''''''''''') + '''''''' END' 68           WHEN system_type_id IN (175) /*字符串数据类型:char*/ 69               THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE '''''''' + CAST(REPLACE(' + name + ', '''''''' ,'''''''''''') AS CHAR(' + CAST(max_length AS VARCHAR) + ')) + '''''''' END' 70           WHEN system_type_id IN (239) /*nicode字符串数据类型:nchar*/ 71               THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE ''N'''''' + CAST(REPLACE(' + name + ', '''''''' ,'''''''''''') AS CHAR(' + CAST(max_length AS VARCHAR) + ')) + '''''''' END' 72           ELSE '''NULL'''  END 73       FROM sys.columns  74       WHERE object_id = @intTableID 75   ) AS T; 76  77   SET @chvnInsertIntoBoday = N'''INSERT INTO '+ @chvnSchemaTableName + N' (' + @chvnColumnNames + N')''';   78   -- 方式一、代码格式使用了GOTO和Label 79   --BEGIN 80   --  IF @bitIsSingleRow = 0 /*多行模式*/ 81   --  BEGIN   82   --    SET @chvnTSQL = N'SELECT ''SELECT '' + ' + @chvnColumnValues + ' AS RowData, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNum FROM ' + @chvnSchemaTableName 83  84  85   --    -- 此处不能使用GOTO WhereCondition;,因为之后的代码不会被执行 86   --    IF @chvnWhere > '' 87   --    BEGIN 88   --      SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere; 89   --    END 90  91   --    -- 处理多行模式,需要使用ROW_NUMBER窗口函数     92   --    SET @chvnTSQL = N'SELECT CASE WHEN T.rownum = 1 THEN REPLICATE(N'' '', LEN(N''UNION ALL '') + 1) + T.RowData ELSE N''UNION ALL '' + T.RowData END' + 93   --            N' FROM (' + @chvnTSQL + N') AS T'; 94  95   --    SET @chvnTSQL = N'SELECT '+ @chvnInsertIntoBoday + N';' + 96   --            @chvnTSQL; 97  98   --    GOTO MultiRow; 99   --  END100   --  ELSE IF @bitIsSingleRow = 1 /*当行模式*/101   --  BEGIN102   --    SET @chvnTSQL = N'SELECT ' + @chvnInsertIntoBoday + 103   --            N' + ''VALUES('' + ' + @chvnColumnValues + ' + '');'' FROM ' + @chvnSchemaTableName;104 105   --    GOTO WhereCondition;106   --  END107 108   --  -- where查询条件109   --  WhereCondition:110   --  IF @chvnWhere > ''111   --  BEGIN112   --    SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere;113   --  END114 115 116   --  MultiRow:/*多行模式GOTO的Label空标记*/117 118   --END119 120   -- 方式二、存在部分代码的冗余121   BEGIN122     IF @bitIsSingleRow = 0 /*多行模式*/123     BEGIN  124       SET @chvnTSQL = N'SELECT ''SELECT '' + ' + @chvnColumnValues + ' AS RowData, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNum FROM ' + @chvnSchemaTableName  125 126       IF @chvnWhere > ''127       BEGIN128         SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere;129       END130 131       -- 多行模式特殊代码,需要使用ROW_NUMBER窗口函数  132       SET @chvnTSQL = N'SELECT CASE WHEN T.rownum = 1 THEN REPLICATE(N'' '', LEN(N''UNION ALL '') + 1) + T.RowData ELSE N''UNION ALL '' + T.RowData END' +133               N' FROM (' + @chvnTSQL + N') AS T';134 135       SET @chvnTSQL = N'SELECT '+ @chvnInsertIntoBoday + N';' +136               @chvnTSQL;137     END138     ELSE IF @bitIsSingleRow = 1 /*单行模式*/139     BEGIN140       SET @chvnTSQL = N'SELECT ' + @chvnInsertIntoBoday + 141               N' + ''VALUES('' + ' + @chvnColumnValues + ' + '');'' FROM ' + @chvnSchemaTableName;142 143       IF @chvnWhere > ''144       BEGIN145         SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere;146       END147     END148   END149 150   PRINT @chvnTSQL;    151   EXEC(@chvnTSQL);152 END153 GO

 

    为了测试以上存储的效果,下面准备一个有数据的数据表,T-SQL代码如下:



 1 IF OBJECT_ID(N'dbo.UserLoginInfo', N'U') IS NOT NULL 2 BEGIN 3   DROP TABLE dbo.UserLoginInfo; 4 END 5 GO 6  7 -- create testing table UserLoginInfo 8 CREATE TABLE dbo.UserLoginInfo ( 9   ID INT IDENTITY(1, 1) PRIMARY KEY,10   Name VARCHAR(50) NOT NULL,11   LoginTime DATETIME NOT NULL12 );13 GO14 15 -- insert testing data16 INSERT dbo.UserLoginInfo (Name, LoginTime) VALUES 17 ('zhang', '2015-11-10 12:01:50')18 ,('li', '2015-11-11 11:01:50')19 ,('wang', '2015-11-9 11:01:50')20 ,('zhang', '2015-11-11 12:01:50')21 ,('li', '2015-11-11 12:01:50')22 ,('wang', '2015-11-11 11:01:50')23 ,('zhang', '2015-11-12 12:01:50')24 ,('li', '2015-11-13 13:01:50')25 ,('wang', '2015-11-12 11:01:50')26 ,('zhang', '2015-11-13 12:01:50')27 ,('li', '2015-11-14 11:01:50')28 ,('wang', '2015-11-14 11:01:50')29 ,('zhang', '2015-11-10 12:01:50')30 ,('li', '2013-10-05 11:01:50')31 ,('li', '2013-10-06 11:01:50')32 ,('li', '2014-10-05 11:01:50')33 ,('li', '2014-10-06 11:01:50')34 ,('li', '2015-10-05 11:01:50')35 ,('li', '2015-10-06 11:01:50')36 ,('li', '2015-11-10 11:01:50')37 ,('li', '2015-11-11 11:01:50')38 ,('wang', '2015-11-09 11:01:50')39 ,('zhang', '2015-11-11 12:01:50')40 ,('li', '2015-11-11 12:01:50')41 ,('wang', '2015-11-11 11:01:50')42 ,('zhang', '2015-11-12 12:01:50')43 ,('li', '2015-11-13 13:01:50')44 ,('wang', '2015-11-12 11:01:50')45 ,('zhang', '2015-11-13 12:01:50')46 ,('li', '2015-11-14 11:01:50')47 ,('wang', '2015-11-14 11:01:50');48 GO

 

先测试单行模式的效果,相应的T-SQL代码如下:



1 EXEC dbo.usp_GetInsertSQL2   @chvnTable = N'UserLoginInfo',      -- nvarchar(128)3   @chvnWhere = N'',            -- nvarchar(1000)4   @bitIsSingleRow = 1;          -- bit5 GO

执行后的查询结果如下:




 
 
再测试多行模式的效果,相应的T-SQL代码如下:
1 EXEC dbo.usp_GetInsertSQL2   @chvnTable = N'UserLoginInfo',      -- nvarchar(128)3   @chvnWhere = N'',            -- nvarchar(1000)4   @bitIsSingleRow = 0;          -- bit5 GO

 


执行后的查询效果如下:


注意:多行模式,还需要将以上的两个结果前后合并在一个文件就可以啦。
 
博友如有其他更好的解决方案,也请不吝赐教,万分感谢。