文章继续 桦仔兄的文章将表里的数据批量生成INSERT语句的存储过程 增强版继续增强...
本来打算将该内容回复于桦仔兄的文章的下面的，但是不知为何博客园就是不让提交！....
所以在这里贴出来吧，算作继续增加文章中解决的：根据查询条件自动生成插入脚本的需求，其实这种需求还是蛮常见的。
本文着重解决了文中的脚本的schema问题，给调整了下，现在脚本能自动识别出不同的schema下同名的表的语句
修改后脚本如下：
-- Author:<桦仔>-- Blog: </lyhabc/>-- Create date: </10/18>-- Description: <根据查询条件导出表数据的insert脚本>-- =============================================ALTER PROCEDURE InsertGenerator(@tableName NVARCHAR(MAX),@whereClause NVARCHAR(MAX))AS --Then it includes a cursor to fetch column specific information (column name and the data type thereof) --from information_schema.columns pseudo entity and loop through for building the INSERT and VALUES clauses --of an INSERT DML statement.DECLARE @string NVARCHAR(MAX) --for storing the first half of INSERT statementDECLARE @stringData NVARCHAR(MAX) --for storing the data (VALUES) related statementDECLARE @dataType NVARCHAR(MAX) --data types returned for respective columnsDECLARE @schemaName NVARCHAR(MAX) --schema name returned from sys.schemasDECLARE @schemaNameCount int--shema countDECLARE @QueryString NVARCHAR(MAX) -- provide for the whole query, set @QueryString=' '--如果有多个schema，选择其中一个schemaSELECT @schemaNameCount=COUNT(*)FROM sys.tables tINNER JOIN sys.schemas s ON t.schema_id = s.schema_idWHERE t.name = @tableNameWHILE(@schemaNameCount>0)BEGIN--如果有多个schema，依次指定select @schemaName = name from (SELECT ROW_NUMBER() over(order by s.schema_id) RowID,s.nameFROM sys.tables tINNER JOIN sys.schemas s ON t.schema_id = s.schema_idWHERE t.name = @tableName) as vwhere RowID=@schemaNameCount--Declare a cursor to retrieve column specific information --for the specified tableDECLARE cursCol CURSOR FAST_FORWARDFORSELECT column_name ,data_typeFROM information_schema.columnsWHERE table_name = @tableNameAND table_schema = @schemaNameOPEN cursColSET @string = 'INSERT INTO [' + @schemaName + '].[' + @tableName + ']('SET @stringData = ''DECLARE @colName NVARCHAR(500)FETCH NEXT FROM cursCol INTO @colName, @dataTypePRINT @schemaNamePRINT @colNameIF @@fetch_status <> 0BEGINPRINT 'Table ' + @tableName + ' not found, processing skipped.'CLOSE curscolDEALLOCATE curscolRETURNENDWHILE @@FETCH_STATUS = 0BEGINIF @dataType IN ( 'varchar', 'char', 'nchar', 'nvarchar' )BEGINSET @stringData = @stringData + '''''''''+isnull(' + @colName + ','''')+'''''',''+'ENDELSEIF @dataType IN ( 'text', 'ntext' ) --if the datatype --is text or something else BEGINSET @stringData = @stringData + '''''''''+isnull(cast(' + @colName + ' as nvarchar(max)),'''')+'''''',''+'ENDELSEIF @dataType = 'money' --because money doesn't get converted --from varchar implicitlyBEGINSET @stringData = @stringData+ '''convert(money,''''''+isnull(cast(' + @colName+ ' as nvarchar(max)),''0.0000'')+''''''),''+'ENDELSEIF @dataType = 'datetime'BEGINSET @stringData = @stringData+ '''convert(datetime,''''''+isnull(cast(' + @colName + ' as nvarchar(max)),''0'')+''''''),''+'ENDELSEIF @dataType = 'image'BEGINSET @stringData = @stringData + '''''''''+isnull(cast(convert(varbinary,' + @colName + ') as varchar(6)),''0'')+'''''',''+'ENDELSE --presuming the data type is int,bit,numeric,decimal BEGINSET @stringData = @stringData + '''''''''+isnull(cast(' + @colName + ' as nvarchar(max)),''0'')+'''''',''+'ENDSET @string = @string + '[' + @colName + ']' + ','FETCH NEXT FROM cursCol INTO @colName, @dataTypeEND--After both of the clauses are built, the VALUES clause contains a trailing comma which needs to be replaced with a single quote. The prefixed clause will only face removal of the trailing comma.DECLARE @Query NVARCHAR(MAX) -- provide for the whole query, -- you may increase the sizePRINT @whereClauseIF ( @whereClause IS NOT NULLAND @whereClause <> '')BEGIN SET @query = 'SELECT ''' + SUBSTRING(@string, 0, LEN(@string))+ ') VALUES(''+ ' + SUBSTRING(@stringData, 0,LEN(@stringData) - 2)+ '''+'')'' FROM ' +@schemaName+'.'+ @tableName + ' WHERE ' + @whereClausePRINT @query-- EXEC sp_executesql @query --load and run the built query--Eventually, close and de-allocate the cursor created for columns information.ENDELSEBEGIN SET @query = 'SELECT ''' + SUBSTRING(@string, 0, LEN(@string))+ ') VALUES(''+ ' + SUBSTRING(@stringData, 0,LEN(@stringData) - 2)+ '''+'')'' FROM ' + @schemaName+'.'+ @tableNameENDCLOSE cursColDEALLOCATE cursColSET @schemaNameCount=@schemaNameCount-1IF(@schemaNameCount=0)BEGINSET @QueryString=@QueryString+@queryENDELSEBEGINSET @QueryString=@QueryString+@query+' UNION ALL 'ENDPRINT convert(varchar(max),@schemaNameCount)+'---'+@QueryStringENDEXEC sp_executesql @QueryString --load and run the built query--Eventually, close and de-allocate the cursor created for columns information.
1、测试脚本如下：
INSERT INTO test1.[customer]([city],[region]) VALUES('2','3')InsertGenerator 'customer', null
效果如下：
2、增加筛选条件
InsertGenerator 'customer', 'city=1'
其它内容可以参照桦仔兄的原文章地址。
