SQL Server 生成Insert脚本的实现(存储过程)

/*
	功能:	生成指定数据的insert 脚本
					
	作者：一笑
	日期：2022.6.15
	修改：
*/	
ALTER Proc [dbo].[P_GetInsertScripts](
	@TmpTableName varchar(100), -- 临时表名
	@SrcTableName varchar(100)  -- 导出数据的表名
)
as
begin	
	SET NOCOUNT ON	
	-- 临时表Id
	declare @ObjId int = (select top 1 id from tempdb..sysobjects  where xtype = 'U' and name like @TmpTableName + '%');
	declare @SqlText nvarchar(max) = '';
	declare @FiledNames varchar(8000) = ''; -- 新增的字段
	-- 列名游标	
	declare C_Column cursor for 
		select 
			t.name as ColName,
			a.name as ColType
		from tempdb..syscolumns t
		inner join systypes a on t.xtype = a.xusertype 
		where id = @ObjId order by colid
	declare 
		@ColName varchar(100),
		@ColType varchar(100);	
	open C_Column;
	fetch next from C_Column into @ColName,@ColType;
	while @@FETCH_STATUS = 0
		begin
			set @FiledNames += @ColName+',';
			 --拼接查询SQL
			if (@ColType in ('int','decimal','bit'))
			begin
				set @SqlText += '''''+Cast('+@ColName+' as varchar(1000))+'',''+'				
			end else
			begin
				set @SqlText += '''''''''+IsNull(Cast('+@ColName+' as varchar(1000)),'''')+'''''',''+'					
			end	
			fetch next from C_Column into @ColName,@ColType
		end
	close C_Column;
	deallocate C_Column;
		
	-- 去除字段最后的，
	set @FiledNames = STUFF(@FiledNames,len(@FiledNames),1,'')
	-- 去除最后的 [,+]
	set @SqlText = STUFF(@SqlText,len(@SqlText)-2,3,'''')
	-- 添加 前部分的 select ... 语句
	set @SqlText = 'select ' + STUFF(@SqlText,1,1,'''select ')
	-- 添加后部分的 ... from 语句
	set @SqlText = STUFF(@SqlText,LEN(@SqlText)-1,1,''' union all') + ' as Col from ' + @TmpTableName	
	-- 临时脚本数据表
	declare @SqlTable table(SQLText varchar(2000))
	-- 插入select ... union all 脚本到临时表
	insert into @SqlTable exec ('select * from (' + @SqlText + ') t')
	
	declare @SQL2 nvarchar(max) = 
	(
	SELECT 
		 stuff((
			SELECT char(10) + SQLText  --
			FROM @SqlTable        
			FOR XML path('')), 1, 1, '')
	 )
	 
	set @SQL2 = STUFF(@SQL2,LEN(@SQL2)-9,10,'')

	Print 'insert into '+@SrcTableName + '(' + @FiledNames + ')' + char(10) + @SQL2

	--exec ('drop table '+ @TmpTableName)

	SET NOCOUNT OFF
end

示例：导出 Dict_Common 表的前20行数据

 

       将查询的字段及数据，放到临时表 #TmpData 中

select top 100 DictType,DictCode,DictValue,Remark
into #TmpData
from Dict_Common

　　执行生成存储过程，生成Insert脚本

exec [dbo].[P_GetInsertScripts] '#TmpData','Dict_Common'

　　生成insert 脚本结果如下：