答复: 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