将MSSQL表数据转成SQL语句

34222

创建一个把表记录转换成SQL语句

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

CREATE PROC [dbo].[up_tb_tosql] @tbname varchar(200) = '',<span style="white-space:pre">  </span>--表名 @rownum varchar(10) ='1000'<span style="white-space:pre">  </span>--转换条数 as BEGIN --表名检查 IF @tbname = '' BEGIN     return 0; END --条数验证 IF @rownum is null BEGIN     SET @rownum ='1000'; END --定义变量 DECLARE @colinfo varchar(8000) DECLARE @sqlinfo varchar(8000) DECLARE @sqlcmd varchar(8000) DECLARE @sqlst varchar(800) DECLARE @sqlend varchar(800) SET @sqlcmd=''; SET @sqlst=''; SET @sqlend=''; --生成列信息 SELECT @colinfo=replace(replace(CAST (( SELECT  case when system_type_id in (61,58) then convert(varchar(20),name,120) else  name end +',' from sys.columns             WHERE  object_name(object_id)=@tbname             FOR XML PATH(''), TYPE) AS NVARCHAR(MAX) ) ,'</name>',''),'<name>',''); SELECT @colinfo=Left(@colinfo,len(@colinfo)-1); --拼接列名 SELECT @sqlinfo=replace(replace(CAST (( SELECT  case when system_type_id in (58,61) then '+convert(varchar(20),isnull('+name+',''''),120)+'''''',''''''' else  '+replace(CAST(isnull('+name+','''') as varchar(2000)),'''''''','''')+'''''',''''''' end from sys.columns             WHERE object_name(object_id)=@tbname             FOR XML PATH(''), TYPE) AS NVARCHAR(MAX) ) ,'</name>',''),'<name>',''); SELECT @sqlinfo=Left(@sqlinfo,len(@sqlinfo)-8); --查询是否自增 IF exists (SELECT * FROM  sys.columns  WHERE  object_name(object_id)=@tbname and is_identity=1) BEGIN     SET @sqlst='SELECT ''SET identity_insert ['+@tbname+'] ON '' UNION ALL ';     SET @sqlend='UNION ALL SELECT '' SET identity_insert ['+@tbname+'] OFF  '''; END --拼写整个SQL SELECT @sqlcmd=@sqlst+' SELECT TOP '+@rownum+' ''INSERT INTO ['+@tbname+']('+@colinfo+') SELECT '''''''+@sqlinfo+ '+'''''';'' FROM '+@tbname+' '+@sqlend; --SELECT @sqlcmd,@sqlst,@colinfo,@sqlinfo,@tbname,@sqlend,@rownum; --执行SQL exec (@sqlcmd); --print @sqlcmd; end

1 2	调用方式 up_tb_tosql('t_tbnam','10000');