SQL Server中數(shù)據(jù)行批量插入腳本的存儲(chǔ)實(shí)現(xiàn)
更新時(shí)間：2015年12月24日 14:54:31 作者：劍走江湖
這篇文章主要介紹了SQL Server中數(shù)據(jù)行批量插入腳本的存儲(chǔ)實(shí)現(xiàn) 的相關(guān)資料,需要的朋友可以參考下
無(wú)意中看到朋友寫的一篇文章“將表里的數(shù)據(jù)批量生成INSERT語(yǔ)句的存儲(chǔ)過(guò)程的實(shí)現(xiàn)”。我仔細(xì)看文中的兩個(gè)存儲(chǔ)代碼，自我感覺(jué)兩個(gè)都不太滿意，都是生成的單行模式的插入，數(shù)據(jù)行稍微大些性能會(huì)受影響的。所在公司本來(lái)就存在第二個(gè)版本的類似實(shí)現(xiàn)，但是是基于多行模式的，還是需要手工添加UNAION ALL來(lái)滿足多行模式的插入。看到這篇博文和基于公司數(shù)據(jù)行批量腳本的存儲(chǔ)的缺點(diǎn)，這次改寫和增強(qiáng)該存儲(chǔ)的功能。
本存儲(chǔ)運(yùn)行于SQL Server 2005或以上版本，T-SQL代碼如下：
IF OBJECT_ID(N'dbo.usp_GetInsertSQL', 'P') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.usp_GetInsertSQL;
END
GO
--==================================
-- 功能: 獲取數(shù)據(jù)表記錄插入的SQL腳本
-- 說(shuō)明: 具體實(shí)現(xiàn)闡述
-- 作者: XXX
-- 創(chuàng)建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改內(nèi)容描述
--==================================
CREATE PROCEDURE dbo.usp_GetInsertSQL
(
@chvnTable NVARCHAR(), -- 數(shù)據(jù)表名稱(建議只使用表名稱，不要帶有分隔符[])
@chvnWhere NVARCHAR() = N'', -- where查詢條件(不帶WHERE關(guān)鍵字）
@bitIsSingleRow BIT = -- 是否單行模式，默認(rèn)為單行模式(單行模式為單行INSERT INTO VALUES格式；非單行模式（多行模式）為多行INSERT INTO SELECT格式)
)
--$Encode$--
AS
BEGIN
SET NOCOUNT ON;
SET @bitIsSingleRow = ISNULL(@bitIsSingleRow, );
DECLARE
@intTableID AS INT,
@chvnSchemaTableName NVARCHAR();/*格式：[schema].[table]--++++++（各部分對(duì)應(yīng)字符數(shù)）*/
SELECT
@intTableID = ,
@chvnSchemaTableName = N'';
SELECT
@intTableID = object_id
,@chvnSchemaTableName = QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(@chvnTable) /*組合架構(gòu)名稱和表名稱的連接*/
FROM sys.objects
WHERE name = @chvnTable
AND type = 'U';
DECLARE
@chvnColumnNames NVARCHAR(), -- 字段列名集，多個(gè)以逗號(hào)','分隔，格式如：[column_name],[column_name],...
@chvnColumnValues AS NVARCHAR(MAX); -- 字段列值集，多個(gè)以逗號(hào)','分隔
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 (, , , , , , , , , , ) /*數(shù)字?jǐn)?shù)據(jù)類型：整數(shù)數(shù)據(jù)類型(bit、tinyint、smallint、int、bigint)，帶精度和小數(shù)的數(shù)據(jù)類型(decimal、numeric)和貨幣數(shù)據(jù)類型（monery和smallmoney*/
THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE CAST(' + name + ' AS VARCHAR) END'
WHEN system_type_id IN (, , , , ) /*日期和時(shí)間數(shù)據(jù)類型：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 () /*字符串?dāng)?shù)據(jù)類型：varchar*/
THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE '''''''' + REPLACE(' + name + ', '''''''', '''''''''''') + '''''''' END'
WHEN system_type_id IN () /*Unicode字符串?dāng)?shù)據(jù)類型：nvarchar*/
THEN 'CASE WHEN '+ name + ' IS NULL THEN ''NULL'' ELSE ''N'''''' + REPLACE(' + name + ', '''''''','''''''''''') + '''''''' END'
WHEN system_type_id IN () /*字符串?dāng)?shù)據(jù)類型：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字符串?dāng)?shù)據(jù)類型：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;,因?yàn)橹蟮拇a不會(huì)被執(zhí)行
-- IF @chvnWhere > ''
-- BEGIN
-- SET @chvnTSQL = @chvnTSQL + ' WHERE ' + @chvnWhere;
-- END
-- -- 處理多行模式，需要使用ROW_NUMBER窗口函數(shù)
-- 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 = /*當(dāng)行模式*/
-- 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空標(biāo)記*/
--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窗口函數(shù)
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
為了測(cè)試以上存儲(chǔ)的效果，下面準(zhǔn)備一個(gè)有數(shù)據(jù)的數(shù)據(jù)表，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
先測(cè)試單行模式的效果，相應(yīng)的T-SQL代碼如下：
EXEC dbo.usp_GetInsertSQL
@chvnTable = N'UserLoginInfo', -- nvarchar()
@chvnWhere = N'', -- nvarchar()
@bitIsSingleRow = ; -- bit
GO
執(zhí)行后的查詢結(jié)果如下：
再測(cè)試多行模式的效果，相應(yīng)的T-SQL代碼如下：
EXEC dbo.usp_GetInsertSQL @chvnTable = N'UserLoginInfo', -- nvarchar() @chvnWhere = N'', -- nvarchar() @bitIsSingleRow = ; -- bit GO
執(zhí)行后的查詢效果如下：
注意：多行模式，還需要將以上的兩個(gè)結(jié)果前后合并在一個(gè)文件就可以啦。
以上內(nèi)容是小編給大家分享的SQL Server中數(shù)據(jù)行批量插入腳本的存儲(chǔ)實(shí)現(xiàn)，希望大家喜歡。
相關(guān)文章
世界杯猜想活動(dòng)的各類榜單的SQL語(yǔ)句小結(jié)
自己網(wǎng)站的世界杯猜想活動(dòng)，整理了幾個(gè)排行榜。寫了半個(gè)小時(shí)的SQL，丟了多可惜，放在這里，反正是別人的地盤，不心疼。2010-07-07
SQL命令大全-中英文對(duì)照...
2007-11-11 
索引碎片會(huì)影響數(shù)據(jù)庫(kù)性能,主要有邏輯碎片和物理碎片兩種類型,邏輯碎片導(dǎo)致數(shù)據(jù)頁(yè)順序不連續(xù),而物理碎片影響磁盤讀取性能,解決方法包括重建或重組索引,以及定期維護(hù),下面就一起來(lái)看一下
2024-09-09 
SQL Server誤區(qū)30日談 第4天 DDL觸發(fā)器就是INSTEAD OF觸發(fā)器
DDL觸發(fā)器的實(shí)現(xiàn)原理其實(shí)就是一個(gè)AFTER觸發(fā)器。這個(gè)意思是先發(fā)生DDL操作，然后觸發(fā)器再捕捉操作（當(dāng)然如果你在觸發(fā)器內(nèi)寫了Rollback,則也可能回滾）
2013-01-01 
sqlserver中操作主從關(guān)系表的sql語(yǔ)句
在項(xiàng)目開(kāi)發(fā)中，經(jīng)常出現(xiàn)這樣的需求。在新增或修改一個(gè)主表數(shù)據(jù)時(shí)，對(duì)應(yīng)的從表也要進(jìn)行同步，此時(shí)我們是怎么操作的了？
2011-07-07 
SQL Server 遠(yuǎn)程連接服務(wù)器詳細(xì)配置(sp_addlinkedserver)
這篇文章主要介紹了SQL Server 遠(yuǎn)程連接服務(wù)器詳細(xì)配置(sp_addlinkedserver),需要的朋友可以參考下
2017-01-01 
這篇文章主要介紹了sqlserver:查詢鎖住sql以及解鎖方法，具有一定參考價(jià)值，這里分享給大家，供需要的朋友參考。
2017-10-10 
這篇文章主要介紹了SQL Server中交叉聯(lián)接的用法介紹,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下
2021-04-04 
t-sql/mssql用命令行導(dǎo)入數(shù)據(jù)腳本的SQL語(yǔ)句示例
這篇文章主要介紹了t-sql或mssql用命令行導(dǎo)入數(shù)據(jù)腳本的SQL語(yǔ)句示例，大家參考使用吧
2013-11-11