sql.sql

通过EXCEL VBA提取和插入SQL记录

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RecieveMessage_tmp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_RecieveMessage_tmp]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_RecieveMessage_tmp_save]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_RecieveMessage_tmp_save]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RecieveMessage_tmp_fail_tmp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RecieveMessage_tmp_fail_tmp]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RecieveMessage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RecieveMessage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RecieveMessage_tmp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RecieveMessage_tmp]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RecieveMessage_tmp_fail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RecieveMessage_tmp_fail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RecieveMessage_tmp_true]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RecieveMessage_tmp_true]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RecieveMessage_tmp_true_tmp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RecieveMessage_tmp_true_tmp]
GO

CREATE TABLE [dbo].[RecieveMessage_tmp_fail_tmp] (
	[outlet] [decimal](13, 0) NOT NULL ,
	[Mobile] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Service] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Message] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[RecieveTime] [datetime] NOT NULL ,
	[GWRecieveTime] [datetime] NULL ,
	[State] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RecieveMessage] (
	[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Mobile] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Service] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Message] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[RecieveTime] [datetime] NOT NULL ,
	[GWRecieveTime] [datetime] NULL ,
	[State] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RecieveMessage_tmp] (
	[outlet] [decimal](13, 0) NOT NULL ,
	[Mobile] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Service] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Message] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[RecieveTime] [datetime] NOT NULL ,
	[GWRecieveTime] [datetime] NULL ,
	[State] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RecieveMessage_tmp_fail] (
	[outlet] [decimal](13, 0) NOT NULL ,
	[Mobile] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Service] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Message] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[RecieveTime] [datetime] NOT NULL ,
	[GWRecieveTime] [datetime] NULL ,
	[State] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RecieveMessage_tmp_true] (
	[outlet] [decimal](13, 0) NOT NULL ,
	[Mobile] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Service] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Message] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[RecieveTime] [datetime] NOT NULL ,
	[GWRecieveTime] [datetime] NULL ,
	[State] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RecieveMessage_tmp_true_tmp] (
	[outlet] [decimal](13, 0) NOT NULL ,
	[Mobile] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Service] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Message] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[RecieveTime] [datetime] NOT NULL ,
	[GWRecieveTime] [datetime] NULL ,
	[State] [int] NOT NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.sp_RecieveMessage_tmp (@tran_date datetime)
as

set @tran_date  = DATEADD(HH,12,@tran_date)

--失败表插入数据
truncate table SMS..RecieveMessage_tmp_fail_tmp

insert into SMS..RecieveMessage_tmp_fail_tmp 
select outlet,Mobile,Service,Message,RecieveTime,GWRecieveTime,State from SMS..RecieveMessage_tmp
where (outlet not in (select outlet from SMOrder..vw_Customer)) or (Mobile not in (select Mobile from SMOrder..MobileUser))

insert into SMS..RecieveMessage_tmp_fail
--select * from SMS..RecieveMessage_tmp_fail_tmp
select outlet,Mobile,Service,Message,RecieveTime,GWRecieveTime,State from SMS..RecieveMessage_tmp
where (outlet not in (select outlet from SMOrder..vw_Customer)) or (Mobile not in (select Mobile from SMOrder..MobileUser))

--删除失败的数据
delete from SMS..RecieveMessage_tmp	
where  (outlet not in (select outlet from SMOrder..vw_Customer)) or (Mobile not in (select Mobile from SMOrder..MobileUser))

--获得客户的拜访日期
select outlet,RecieveTime,State as [week] into #outlet from SMS..RecieveMessage_tmp where 1=2

insert into  #outlet
select distinct outlet,'2007-01-01' as RecieveTime, 0 as [week] from SMS..RecieveMessage_tmp

update t1 set [week]=t2.OM06_WEKDAY
from #outlet t1,BASIS..BASIS_OM06 t2 where t1.outlet=t2.OM06_OUTNUM

update #outlet set RecieveTime=DATEADD(dd,2,@tran_date) --没有拜访日期和周日拜访的周三发送
where [week] in (0,7)

update #outlet set RecieveTime=DATEADD(dd,[week]-1,@tran_date)
where [week] in (1,2,3,4,5,6)

--更新SMS..RecieveMessage_tmp中日期
update t1 set t1.RecieveTime=t2.RecieveTime,t1.GWRecieveTime=t2.RecieveTime
from SMS..RecieveMessage_tmp t1,#outlet t2 where t1.outlet=t2.outlet

drop table #outlet

--truncate table SMS..RecieveMessage_tmp  truncate table SMS..RecieveMessage_tmp_fail_tmp 每次导入前如果需要重新导入
--truncate table SMS..RecieveMessage_tmp_true  truncate table SMS..RecieveMessage_tmp_fail 第一次导入前
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.sp_RecieveMessage_tmp_save
as

--失败表插入数据
--truncate table SMS..RecieveMessage_tmp_fail_tmp

insert into SMS..RecieveMessage_tmp_fail_tmp 
select outlet,Mobile,Service,Message,RecieveTime,GWRecieveTime,State from SMS..RecieveMessage_tmp
where (outlet not in (select outlet from SMOrder..vw_Customer)) or (Mobile not in (select Mobile from SMOrder..MobileUser))

insert into SMS..RecieveMessage_tmp_fail
select outlet,Mobile,Service,Message,RecieveTime,GWRecieveTime,State from SMS..RecieveMessage_tmp
where (outlet not in (select outlet from SMOrder..vw_Customer)) or (Mobile not in (select Mobile from SMOrder..MobileUser))

--删除失败的数据
delete from SMS..RecieveMessage_tmp	
where  (outlet not in (select outlet from SMOrder..vw_Customer)) or (Mobile not in (select Mobile from SMOrder..MobileUser))

--发送
insert into SMS..RecieveMessage
select Mobile,Service,Message,RecieveTime,GWRecieveTime,State from SMS..RecieveMessage_tmp
where year(RecieveTime)=year(getdate()) and  month(RecieveTime)=month(getdate()) and day(RecieveTime)=day(getdate())
and State=0

insert into RecieveMessage_tmp_true
select * from SMS..RecieveMessage_tmp
where year(RecieveTime)=year(getdate()) and  month(RecieveTime)=month(getdate()) and day(RecieveTime)=day(getdate())
and State=0

update SMS..RecieveMessage_tmp set State=1
where year(RecieveTime)=year(getdate()) and  month(RecieveTime)=month(getdate()) and day(RecieveTime)=day(getdate())
and State=0

update RecieveMessage_tmp_true set State=1 where State=0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO