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

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

CREATE PROCEDURE [dbo].[proc_intoCancelMobiles] --创建插入号码的存储过程

@fileName VARCHAR(500), www.2cto.com

@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