Sqlserver 下传导入文本文件和后续处理的存储过程

CREATE PROCEDURE [dbo].[proc_intoCancelMobiles]? --创建插入号码的存储过程
??? @fileName VARCHAR(500),
??? @businessId char(32),
??? @whichMonth varchar(7),
??? @busFlag varchar(20)
AS
BEGIN
?
?CREATE TABLE #t (mobile varchar(50),busName varchar(50),startTime datetime,endTime datetime,busFlag varchar(50))
?TRUNCATE TABLE #t? --删除临时表中的数据
?
?EXECUTE('BULK INSERT #t FROM ''' + @fileName + ''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'' )?? ')? --创建临时表 并将倒入的手机号码插入临时表

CREATE TABLE #t2 (id int NOT NULL IDENTITY(1,1), mobile varchar(50),busName varchar(50),startTime datetime,endTime datetime,busFlag varchar(50))
--过滤重复
?TRUNCATE TABLE #t2? --删除临时表中的数据
insert into #t2(mobile,busName,startTime,endTime,busFlag) select?? mobile,busName,startTime,endTime,busFlag from #t where CHARINDEX(busFlag,@busFlag)>0;
?
?CREATE TABLE #t3 (mobile varchar(50),busName varchar(50),startTime datetime,endTime datetime,busFlag varchar(50))
TRUNCATE TABLE #t3 --删除临时表中的数据
insert into #t3 (mobile,busName,startTime,endTime,busFlag) select mobile,busName,startTime,endTime,busFlag from (select min(id) id from #t2 group by mobile) as a inner join #t2 as b on a.id = b.id

truncate table cancel_info_temp

insert into cancel_info_temp(phoneNum,cancelInfoId,businessId,cityName,startTime,endTime,addTime,whichMonth,cityId)
select distinct t.mobile,replace(newid(),'-',''),@businessId,s.cityName,t.startTime,t.endTime,getdate(),@whichMonth,s.cityId? from #t3? as t,segment_num as s
where? substring(t.mobile,1,7) = s.phoneNum;

?

END
GO