将MSSQL表数据转成SQL语句
CREATE PROC .@tbname varchar(200) = '',--表名
@rownum varchar(10) ='1000'--转换条数
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 (( SELECTcase when system_type_id in (61,58) then convert(varchar(20),name,120) elsename end +',' from sys.columns
WHEREobject_name(object_id)=@tbname
FOR XML PATH(''), TYPE) AS NVARCHAR(MAX) ) ,'',''),'','');
SELECT @colinfo=Left(@colinfo,len(@colinfo)-1);
--拼接列名
SELECT @sqlinfo=replace(replace(CAST (( SELECTcase 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) ) ,'',''),'','');
SELECT @sqlinfo=Left(@sqlinfo,len(@sqlinfo)-8);
--查询是否自增
IF exists (SELECT * FROMsys.columnsWHEREobject_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]