【实用SQL脚本】生成数据发布的INSERT语句

核心提示：【问题】DB结构的发布，SQL Server的管理器可以生成脚本；那么数据如何发布呢？【思路】生成INSERT INTO ... VALUES...语句/*-- = -- Author: Yew -- Create date: 2012-05-18 -- Description: 生成数据发布的INSE

【问题】DB结构的发布，SQL Server的管理器可以生成脚本；那么数据如何发布呢？
【思路】生成INSERT INTO ... VALUES...语句
/*-- =============================================
-- Author: Yew
-- Create date: 2012-05-18
-- Description: 生成数据发布的INSERT脚本

----TestCode---------------------------
EXEC [TOOL].[Script_Insert]
@Schema = 'COMMON'
,@Table = 'Config'
,@Filter = '[Key] LIKE ''test%'' '
,@Field0 = '[Key]'
,@Field1 = 'SubKey'
,@Field2 = 'Value'
,@Field3 = null
,@Field4 = null

EXEC [TOOL].[Script_Insert]
@Schema = 'COMMON'
,@Table = 'MasterData'
,@Filter = 'Category = ''SheetItem_PreloadType'' '
,@Field0 = 'Category'
,@Field1 = 'Code'
,@Field2 = 'Name'
,@Field3 = 'Ord'
,@Field4 = null

----History---------------------------
-- =============================================*/
ALTER PROCEDURE [TOOL].[Script_Insert]
@Schema sysname = 'dbo'
,@Table sysname
,@Filter sysname = null
,@Field0 sysname
,@Field1 sysname = null
,@Field2 sysname = null
,@Field3 sysname = null
,@Field4 sysname = null
AS
BEGIN
SET NOCOUNT ON;

-- 0. Define Const
DECLARE @NL varchar(2) --NewLine
SET @NL = char(13) + char(10)

DECLARE @vSql NVARCHAR(max)
,@objName sysname
,@fieldList sysname

-- 1. prepare the statement

IF @Schema = ''
SET @Schema = 'dbo'

SET @objName = @Schema + '.' + @Table

SET @vSql = 'SET QUOTED_IDENTIFIER OFF;

SELECT
"INSERT INTO ' + @objName + '(' + @Field0
IF @Field1 IS NOT NULL
SET @vSql = @vSql + ' ,' + @Field1
IF @Field2 IS NOT NULL
SET @vSql = @vSql + ' ,' + @Field2
IF @Field3 IS NOT NULL
SET @vSql = @vSql + ' ,' + @Field3
IF @Field4 IS NOT NULL
SET @vSql = @vSql + ' ,' + @Field4

SET @vSql = @vSql + ' )'
+ @NL + 'VALUES(" + QuoteName(' + @Field0 + ', Char(39))'

IF @Field1 IS NOT NULL
SET @vSql = @vSql + @NL + ' +", " + QuoteName(IsNull(' + @Field1 + ', ""), Char(39))'
IF @Field2 IS NOT NULL
SET @vSql = @vSql + @NL + ' +", " + QuoteName(IsNull(' + @Field2 + ', ""), Char(39))'
IF @Field3 IS NOT NULL
SET @vSql = @vSql + @NL + ' +", " + QuoteName(IsNull(' + @Field3 + ', ""), Char(39))'
IF @Field4 IS NOT NULL
SET @vSql = @vSql + @NL + ' +", " + QuoteName(IsNull(' + @Field4 + ', ""), Char(39))'

SET @vSql = @vSql + @NL + ' +")"'
+ @NL + 'FROM ' + @objName
IF @Filter IS NOT NULL
SET @vSql = @vSql + @NL + 'WHERE ' + @Filter

-- 2.run it
PRINT @vSql
EXEC(@vSql)
END