SQL导出表中的数据

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

--exec sp_getdata2 'dealreports'

--select * from [user]

--use [ReportReal]
ALTER proc [dbo].[sp_getdata2]
@tabname varchar(50)
as

declare @oid int
select @oid=object_id from  sys.tables where name=@tabname

 

declare @curstr varchar(8000)
set @curstr=''
declare @curstr2 varchar(8000)
set @curstr2=''
declare @curstr3 varchar(8000)
set @curstr3=''
declare @curstr4 varchar(8000)
set @curstr4=''

declare @type varchar(50)
declare @cid int
declare @len int
declare @field varchar(100)

DECLARE b_date CURSOR FOR
select b.name as [type],a.column_id,a.max_length,a.name as [field] from sys.columns a
inner join sys.types b
on a.system_type_id=b.system_type_id and a.user_type_id=b.user_type_id
where object_id=@oid

OPEN b_date
FETCH NEXT FROM b_date INTO @type,@cid,@len,@field

while @@fetch_status = 0
begin

-- Define the parameters
 declare @pname varchar(100)
 set @pname='@'+cast(@oid as varchar)+cast(@cid as varchar )
 declare @ptype varchar(80)
 set @ptype=' '+@type
 declare @plen varchar(10)
 set @plen='('+cast(@len as varchar)+')'
 
 set @curstr2=
  case when @curstr2=''
  then @curstr2
  else @curstr2+'
,'
  end
  +@pname
  +@ptype
  +case when
   @type<>'varchar'
   and
   @type<>'char'
   and
   @type<>'nvarchar'
   and
   @type<>'nchar'
   
  then ''
  else
  @plen
  end
--End parameter define

--Target Field
 set @curstr4=case when @curstr4=''
  then @curstr4
  else @curstr4+'
,'
  end
  +@pname
--End Field
 
--Build statement
 set @curstr3=
   case when @curstr3=''
   then @curstr3
   else
   @curstr3+'+'',''+'
   end
   +
   case when
   @type<>'varchar'
   and
   @type<>'char'
   and
   @type<>'nvarchar'
   and
   @type<>'datetime'
   and
   @type<>'smalldatetime'
   and
   @type<>'nchar'
   then
   'cast(isnull('+@pname+',0) as varchar)+ '' as ['+@field+']'''
   when @type='datetime' or @type='smalldatetime'
   then
   '''''''''+'+'convert(varchar,isnull('+@pname+',getdate()),101)'+'+''''''''' +  '+ ''as ['+@field+']'''
   else '''''''''+'+'replace(isnull('+@pname+',''''),'''''''','''''''''''')'+'+'''''''''+  '+ ''as ['+@field+']'''
   end
--end building

 FETCH NEXT FROM b_date INTO @type,@cid,@len,@field
end
--set @curstr3=@curstr3+''''

--print @curstr2
--print @curstr3
close b_date
DEALLOCATE b_date

set @curstr='

declare
@tepstr varchar(8000),
'
+
 @curstr2
+
'

set @tepstr=''select ''

DECLARE c_date CURSOR FOR
select * from ['+@tabname+']'+'

OPEN c_date
FETCH NEXT FROM c_date INTO
'
+
 @curstr4
+
'

WHILE @@fetch_status = 0
BEGIN
 
 if(len(@tepstr)>3000)
 begin
  print @tepstr
  set @tepstr='' ''
  --select len(@tepstr)
 end
 

 set @tepstr=case when @tepstr=''select '' then @tepstr else
  @tepstr+''
  union
  select '' end
  +'
  +@curstr3
  +'

 FETCH NEXT FROM c_date INTO
 '+
  @curstr4
 +'
END

print @tepstr
close c_date
DEALLOCATE c_date'

exec (@curstr)
--print @curstr