if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[allphone]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[allphone]
GOCREATE TABLE [dbo].[allphone] (
[SPHONEID] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[callCount] [int] NULL ,
[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[schoolAddress] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[familyAddress] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[prizeCount] [int] NULL ,
[Visitor] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[guestBookCount] [int] NULL ,
[wellCount] [int] NULL ,
[Code] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[comfirm] [int] NULL ,
[insertTime] [smalldatetime] NULL
) ON [PRIMARY]if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[telephone_temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[telephone_temp]
GOCREATE TABLE [dbo].[telephone_temp] (
[sphoneid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[guesttime] [smalldatetime] NULL
) ON [PRIMARY]
GO
GO
insert telephone_temp
select '59187500702','2006-1-2 0:04:00' union all
select '5928872519','2006-1-1 23:55:00' union all
select '5967837710','2006-1-1 23:28:00' union all
select '5967837710','2006-1-1 23:25:00' union all
select '5967837710','2006-1-1 23:18:00' union all
select '5967837710','2006-1-1 23:16:00' union all
select '5967837710','2006-1-1 23:12:00' union all
select '5967837710','2006-1-1 23:10:00' union all
select '5972753536','2006-1-1 22:58:00' union all
select '5972753536','2006-1-1 22:57:00' union all
select '5972753536','2006-1-1 22:56:00' union all
select '5972753536','2006-1-1 22:48:00'
goCREATE PROCEDURE telephone_tempToallphone
ASupdate a set [callCount]=a.[callCount]+b.c1
from allphone a,(select sphoneid,count(*) as c1 from telephone_temp group by sphoneid) b
where a.sphoneid=b.sphoneidinsert allphone(sphoneid,callCount,prizeCount,guestBookCount,wellcount,insertTime)
select sphoneid,count(*) as c1,0,0,0,min(guesttime) from telephone_temp b where not exists
(select * from allphone a where a.sphoneid=b.sphoneid) group by sphoneid
GO
exec dbo.telephone_tempToallphone
select * from allphone
drop procedure telephone_tempToallphonedrop table allphone,telephone_temp
drop table [dbo].[allphone]
GOCREATE TABLE [dbo].[allphone] (
[SPHONEID] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[callCount] [int] NULL ,
[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[schoolAddress] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[familyAddress] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[prizeCount] [int] NULL ,
[Visitor] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[guestBookCount] [int] NULL ,
[wellCount] [int] NULL ,
[Code] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[comfirm] [int] NULL ,
[insertTime] [smalldatetime] NULL
) ON [PRIMARY]if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[telephone_temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[telephone_temp]
GOCREATE TABLE [dbo].[telephone_temp] (
[sphoneid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[guesttime] [smalldatetime] NULL
) ON [PRIMARY]
GO
GO
insert telephone_temp
select '59187500702','2006-1-2 0:04:00' union all
select '5928872519','2006-1-1 23:55:00' union all
select '5967837710','2006-1-1 23:28:00' union all
select '5967837710','2006-1-1 23:25:00' union all
select '5967837710','2006-1-1 23:18:00' union all
select '5967837710','2006-1-1 23:16:00' union all
select '5967837710','2006-1-1 23:12:00' union all
select '5967837710','2006-1-1 23:10:00' union all
select '5972753536','2006-1-1 22:58:00' union all
select '5972753536','2006-1-1 22:57:00' union all
select '5972753536','2006-1-1 22:56:00' union all
select '5972753536','2006-1-1 22:48:00'
goCREATE PROCEDURE telephone_tempToallphone
ASupdate a set [callCount]=a.[callCount]+b.c1
from allphone a,(select sphoneid,count(*) as c1 from telephone_temp group by sphoneid) b
where a.sphoneid=b.sphoneidinsert allphone(sphoneid,callCount,prizeCount,guestBookCount,wellcount,insertTime)
select sphoneid,count(*) as c1,0,0,0,min(guesttime) from telephone_temp b where not exists
(select * from allphone a where a.sphoneid=b.sphoneid) group by sphoneid
GO
exec dbo.telephone_tempToallphone
select * from allphone
drop procedure telephone_tempToallphonedrop table allphone,telephone_temp
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------- -------------- ----------- -------------------------------------------------- ----------- ------------------------------------------------------
59187500702 1 NULL NULL NULL 0 NULL 0 0 NULL NULL 2006-01-02 00:04:00
5928872519 1 NULL NULL NULL 0 NULL 0 0 NULL NULL 2006-01-01 23:55:00
5967837710 6 NULL NULL NULL 0 NULL 0 0 NULL NULL 2006-01-01 23:10:00
5972753536 4 NULL NULL NULL 0 NULL 0 0 NULL NULL 2006-01-01 22:48:00（所影响的行数为 4 行）