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