| « | December 2025 | » | | 日 | 一 | 二 | 三 | 四 | 五 | 六 | | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | | | | |
|
| 公告 |
Back Today!
Hold on~
Come on~ |
| 统计 |
blog名称:执著
日志总数:39
评论数量:43
留言数量:0
访问次数:247564
建立时间:2005年3月4日 | |
 [SQLSERVER]公司用的，分号码的一个存储过程 |
| ALTER proc division@infile varchar(1000),--源文件完整路径@out varchar(1000), --目的文件夹（必须存在）@outfile varchar(1000),--目的文件名@num int =30000 --分成多少个一份as
set nocount on
declare @cmd varchar(2000)declare @count int
if exists(select * from tempdb.dbo.sysobjects where name = '##temp' and xtype = 'U')begin drop table ##tempend
create table ##temp(Col001 nvarchar(4000))
set @cmd = 'bcp ##temp in ' + @infile + ' -c -P'exec master..xp_cmdshell @cmd
select identity(int,1,1) as no,Col001 into #temp from ##temp
select @count =count(*) from #tempdeclare @ide int set @ide = 1while @count != 0begin set rowcount @num select * into ##t1 from #temp order by no set @cmd = 'bcp "select Col001 from ##t1" queryout "'+@out +'\'+@outfile+'_'+cast(@ide as varchar(4)) +'.txt" -c -P' exec master..xp_cmdshell @cmd delete from #temp where no in (select no from ##t1) drop table ##t1 set @ide = @ide +1 select @count = count(*) from #temp set rowcount 0endset nocount off | |
|
|
|
|
|
