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

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

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

EXEC dbo.usp_GetInsertSQL
@chvnTable = N’UserLoginInfo’, — nvarchar()
@chvnWhere = N”, — nvarchar()
@bitIsSingleRow = ; — bit
GO