EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
if(object_id('testdb.dbo.tbl') is not null) drop table tbl;
select '201305' 月份,1009 编号,1 属性,'A' 单位,'A1' 部门,'201108' 加入时间,N'研发室' 所属室
into tbl
union all select '201305',1009,1,'B','B1','201207',N'推广室'
union all select '201305',1009,1,'C','C1','201301',N'支撑室'
union all select '201305',1009,1,'D','D1','201109',N'服务室'
union all select '201305',1013,2,'C','C2','201302',N'支撑室'
union all select '201305',1027,2,'A','A3','201007',N'研发室'
if(object_id('tempdb..#tbWhile') is not null) drop table #tbWhile;
select distinct [所属室]
into #tbWhile
from tbl
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
if(object_id('testdb.dbo.tbl') is not null) drop table tbl;
select '201305' 月份,1009 编号,1 属性,'A' 单位,'A1' 部门,'201108' 加入时间,N'研发室' 所属室
into tbl
union all select '201305',1009,1,'B','B1','201207',N'推广室'
union all select '201305',1009,1,'C','C1','201301',N'支撑室'
union all select '201305',1009,1,'D','D1','201109',N'服务室'
union all select '201305',1013,2,'C','C2','201302',N'支撑室'
union all select '201305',1027,2,'A','A3','201007',N'研发室'
if(object_id('tempdb..#tbWhile') is not null) drop table #tbWhile;
select distinct [所属室]
into #tbWhile
from tbl
truncate table temp_tbl;
insert into temp_tbl
select *
FROM [testdb].[dbo].tbl
where [所属室]=@str
set @execStr=N'bcp [testdb].[dbo].temp_tbl out "D:\'+ convert(varchar(20),getdate(),112)+replace(convert(varchar(20),getdate(),114),':','')+'.txt" -T -w'
Exec master..xp_cmdshell @execStr
delete from #tbWhile
where [所属室]=@str;
set @str='';
select top 1 @str=[所属室]
from #tbWhile;
end
#7
不行呀,我的是SQL2005
#8
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
if(object_id('testdb.dbo.tbl') is not null) drop table tbl;
select '201305' 月份,1009 编号,1 属性,'A' 单位,'A1' 部门,'201108' 加入时间,N'研发室' 所属室
into tbl
union all select '201305',1009,1,'B','B1','201207',N'推广室'
union all select '201305',1009,1,'C','C1','201301',N'支撑室'
union all select '201305',1009,1,'D','D1','201109',N'服务室'
union all select '201305',1013,2,'C','C2','201302',N'支撑室'
union all select '201305',1027,2,'A','A3','201007',N'研发室'
if(object_id('tempdb..#tbWhile') is not null) drop table #tbWhile;
select distinct [所属室]
into #tbWhile
from tbl
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
if(object_id('testdb.dbo.tbl') is not null) drop table tbl;
select '201305' 月份,1009 编号,1 属性,'A' 单位,'A1' 部门,'201108' 加入时间,N'研发室' 所属室
into tbl
union all select '201305',1009,1,'B','B1','201207',N'推广室'
union all select '201305',1009,1,'C','C1','201301',N'支撑室'
union all select '201305',1009,1,'D','D1','201109',N'服务室'
union all select '201305',1013,2,'C','C2','201302',N'支撑室'
union all select '201305',1027,2,'A','A3','201007',N'研发室'
if(object_id('tempdb..#tbWhile') is not null) drop table #tbWhile;
select distinct [所属室]
into #tbWhile
from tbl