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

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

本存储运行于SQL Server 2005或以上版本，T-SQL代码如下：

 IF OBJECT_ID（N"dbo.usp_GetInsertSQL", "P"） IS NOT NULL BEGINDROP 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 BEGINSET 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.objectsWHERE 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 （SELECTname AS column_name /*字段列名*/ /*字段列值*/ ,column_value = CASEWHEN 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""" ENDFROM 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 = /*单行模式*/ BEGINSET @chvnTSQL = N"SELECT " + @chvnInsertIntoBoday + N" + ""VALUES（"" + " + @chvnColumnValues + " + ""）;"" FROM " + @chvnSchemaTableName;IF @chvnWhere > ""BEGIN SET @chvnTSQL = @chvnTSQL + " WHERE " + @chvnWhere;END ENDENDPRINT @chvnTSQL;EXEC（@chvnTSQL）; END GO

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

 IF OBJECT_ID（N"dbo.UserLoginInfo", N"U"） IS NOT NULL BEGINDROP 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中数据行批量插入脚本的存储实现，希望大家喜欢。