答复: MSSQL数据导出文本

呵呵，不知道有没有什么bug

create procedure bcp2  
  @tablename varchar(40), 
  @where_statement varchar(255) 
as  
begin 
declare @sqltext varchar(6000) 
declare @cname varchar(40) 
declare @typ int
 
--select @tablename = 'tStore' 
 
---构造insert 语句 
 
--Adaptive  Server has expanded all '*' elements in the following statement 
select sc.id, sc.number, sc.colid, sc.status, sc.type, sc.length, sc.offset, sc.usertype, sc.cdefault, sc.domain, sc.name, sc.printfmt, sc.prec, sc.scale, sc.remote_type, sc.remote_name, sc.xstatus, sc.xtype, sc.xdbid, sc.accessrule, sc.status2 into #t 
  from syscolumns sc, sysobjects so 
where sc.id = so.id 
   and so.name = @tablename 
 
select @sqltext = 'insert into ' + @tablename + '(' 
while exists(select 1 from #t) 
begin 
  set rowcount 1 
  select  @cname = name from #t 
 
  select @sqltext  = @sqltext + @cname + ',' 
  delete #t 
  set rowcount 0 
end 
select @sqltext = substring(@sqltext, 1, char_length(@sqltext) - 1) + ')' 
 
drop table #t 
---构造insert 语句 end 
 
---构造values 语句 
 
--Adaptive Server has expanded all '*' elements in the following statement 
select sc.id, sc.number, sc.colid, sc.status, sc.type, sc.length, sc.offset, sc.usertype, sc.cdefault, sc.domain, sc.name, sc.printfmt, sc.prec, sc.scale, sc.remote_type, sc.remote_name, sc.xstatus, sc.xtype, sc.xdbid, sc.accessrule, sc.status2 into #t1 
  from syscolumns sc, sysobjects so 
where sc.id = so.id 
   and so.name = @tablename 
--select * from #t1 
 
select @sqltext = @sqltext  + 'values(' 
while exists(select 1 from #t1) 
begin 
  set rowcount 1 
  select  @cname = name, @typ = type from #t1 
 
  if CHARINDEX('n', @cname) = 1 
    select @sqltext  = @sqltext + ''' + convert(varchar, ' + @cname + ') + '',' 
  else if @typ =38
    select @sqltext  = @sqltext + ''' + convert(varchar, ' + @cname + ') + '',' 
  else if @typ =50
    select @sqltext  = @sqltext + ''' + convert(varchar, ' + @cname + ') + '',' 
  else if @typ =56
    select @sqltext  = @sqltext + ''' + convert(varchar, ' + @cname + ') + '',' 
  else if @typ =52
    select @sqltext  = @sqltext + ''' + convert(varchar, ' + @cname + ') + '',' 
  else if @typ =35
    select @sqltext  = @sqltext + ''''''' + convert(varchar, ' + @cname + ') + '''''',' 
  else if CHARINDEX('d', @cname) = 1 
    select @sqltext  = @sqltext + ''''''' + convert(varchar, ' + @cname + ') + '''''',' 
  else select @sqltext  = @sqltext + ''''''' + ' + @cname + ' + '''''',' 
 
  delete #t1 
  set rowcount 0 
end 
select @sqltext = substring(@sqltext, 1, char_length(@sqltext) - 1)  
select @sqltext = 'select ''' + @sqltext + ')'' from ' + @tablename + ' ' + @where_statement 
--select @sqltext = 'insert into t3 select sqltext=''' + @sqltext + ')''  from ' + @tablename 
print  @sqltext 
--select char_length(@sqltext) 
--exec( 'create table t3(sc1 text)') 
 
exec( @sqltext) 
--exec( 'select * from t3  go drop table t3') 
 
drop table #t1 
---构造values 语句 end 
 
--select convert(varchar(5000), 'insert into tStore(sStoreNO, sStoreDesc) values (''' + sStoreNO+ ''', ''' + sStoreDesc + ''')') from tStore 
 
--drop table t1 
--create table t1(sc1 varchar(20), nc2 int, dc3 datetime) 
 
end