SQL Server中數據行批量拔出劇本的存儲完成。本站提示廣大學習愛好者：（SQL Server中數據行批量拔出劇本的存儲完成）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL Server中數據行批量拔出劇本的存儲完成正文
有意中看到同伙寫的一篇文章“將內外的數據批量生成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')''';
-- 方法1、代碼格局應用了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
-- 方法2、存在部門代碼的冗余
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中數據行批量拔出劇本的存儲完成，願望年夜家愛好。
