你用如下两句再试试.(建议对LINKMAN_PHONE,SOCIAL_ID等建立索引)insert dbo.fs_temp_fix select distinct a.*,b.groupid,2,0,0 from dbo.fs_temp_nonefix a,fs_temp_fix b where (ISNUMERIC(a.LINKMAN_PHONE)=1 and a.LINKMAN_PHONE =b.LINKMAN_PHONE) or (a.SOCIAL_ID =b.SOCIAL_ID)insert dbo.fs_temp_fix select a.*,b.groupid,2,0,0 from dbo.fs_temp_nonefix a,fs_temp_fix b where (ISNUMERIC(a.LINKMAN_PHONE)=1 and a.LINKMAN_PHONE =b.LINKMAN_PHONE) or (a.SOCIAL_ID =b.SOCIAL_ID)
ALTER procedure [dbo].[sp_e9_model_table_2] as begin if exists (select name from sysobjects where type='u' and name ='fs_temp_fix_1') begin truncate table fs_temp_fix_1 drop table fs_temp_fix_1 end select * into fs_temp_fix_1 from fs_temp_fix where 1=2insert into fs_temp_fix_1 select distinct a.*,b.groupid,1,0,0 from dbo.fs_temp_nonefix a,fs_temp_fix b where a.SOCIAL_ID =b.SOCIAL_ID and a.serv_id not in(select fix1.serv_id from fs_temp_fix_1 fix1) union select distinct a.*,b.groupid,2,0,0 from dbo.fs_temp_nonefix a,fs_temp_fix b where ISNUMERIC(a.LINKMAN_PHONE)=1 and a.LINKMAN_PHONE =b.LINKMAN_PHONE and a.SOCIAL_ID <>b.SOCIAL_ID and a.serv_id not in(select fix2.serv_id from fs_temp_fix_1 fix2)--------将结果追加到[fs_temp_fix]表 insert into [fs_temp_fix] select * from [fs_temp_fix_1] end 我用的是上面这个, 楼上的select a.*,b.groupid,2,0,0 ,这里这个2是a.LINKMAN_PHONE =b.LINKMAN_PHONE时候为2,而a.SOCIAL_ID =b.SOCIAL_ID的时候则为1哦
ALTER procedure [dbo].[sp_e9_model_table_2] as begin if exists (select name from sysobjects where type='u' and name ='fs_temp_fix_1') begin truncate table fs_temp_fix_1 drop table fs_temp_fix_1 end select * into fs_temp_fix_1 from fs_temp_fix where 1=2insert into fs_temp_fix_1 select distinct a.*,b.groupid,1,0,0 from dbo.fs_temp_nonefix a,fs_temp_fix b where a.SOCIAL_ID =b.SOCIAL_ID and a.serv_id not in(select fix1.serv_id from fs_temp_fix_1 fix1) union select distinct a.*,b.groupid,2,0,0 from dbo.fs_temp_nonefix a,fs_temp_fix b where ISNUMERIC(a.LINKMAN_PHONE)=1 and a.LINKMAN_PHONE =b.LINKMAN_PHONE and a.SOCIAL_ID <>b.SOCIAL_ID and a.serv_id not in(select fix2.serv_id from fs_temp_fix_1 fix2)--------将结果追加到[fs_temp_fix]表 insert into [fs_temp_fix] select * from [fs_temp_fix_1] end 我用的是上面这个, 楼上的select a.*,b.groupid,2,0,0 ,这里这个2是a.LINKMAN_PHONE =b.LINKMAN_PHONE时候为2,而a.SOCIAL_ID =b.SOCIAL_ID的时候则为1哦
那也就是说fs_temp_fix表中有记录了,还插入干嘛?
select distinct a.*,b.groupid,2,0,0 from dbo.fs_temp_nonefix a,fs_temp_fix b
where (ISNUMERIC(a.LINKMAN_PHONE)=1 and a.LINKMAN_PHONE =b.LINKMAN_PHONE) or (a.SOCIAL_ID =b.SOCIAL_ID)insert dbo.fs_temp_fix
select a.*,b.groupid,2,0,0 from dbo.fs_temp_nonefix a,fs_temp_fix b
where (ISNUMERIC(a.LINKMAN_PHONE)=1 and a.LINKMAN_PHONE =b.LINKMAN_PHONE) or (a.SOCIAL_ID =b.SOCIAL_ID)
ALTER procedure [dbo].[sp_e9_model_table_2]
as
begin
if exists (select name from sysobjects where type='u' and name ='fs_temp_fix_1')
begin
truncate table fs_temp_fix_1
drop table fs_temp_fix_1
end
select * into fs_temp_fix_1 from fs_temp_fix where 1=2insert into fs_temp_fix_1
select distinct a.*,b.groupid,1,0,0 from dbo.fs_temp_nonefix a,fs_temp_fix b
where a.SOCIAL_ID =b.SOCIAL_ID and a.serv_id not in(select fix1.serv_id from fs_temp_fix_1 fix1)
union
select distinct a.*,b.groupid,2,0,0 from dbo.fs_temp_nonefix a,fs_temp_fix b
where ISNUMERIC(a.LINKMAN_PHONE)=1 and a.LINKMAN_PHONE =b.LINKMAN_PHONE and a.SOCIAL_ID <>b.SOCIAL_ID
and a.serv_id not in(select fix2.serv_id from fs_temp_fix_1 fix2)--------将结果追加到[fs_temp_fix]表
insert into [fs_temp_fix] select * from [fs_temp_fix_1]
end
我用的是上面这个,
楼上的select a.*,b.groupid,2,0,0 ,这里这个2是a.LINKMAN_PHONE =b.LINKMAN_PHONE时候为2,而a.SOCIAL_ID =b.SOCIAL_ID的时候则为1哦
ALTER procedure [dbo].[sp_e9_model_table_2]
as
begin
if exists (select name from sysobjects where type='u' and name ='fs_temp_fix_1')
begin
truncate table fs_temp_fix_1
drop table fs_temp_fix_1
end
select * into fs_temp_fix_1 from fs_temp_fix where 1=2insert into fs_temp_fix_1
select distinct a.*,b.groupid,1,0,0 from dbo.fs_temp_nonefix a,fs_temp_fix b
where a.SOCIAL_ID =b.SOCIAL_ID and a.serv_id not in(select fix1.serv_id from fs_temp_fix_1 fix1)
union
select distinct a.*,b.groupid,2,0,0 from dbo.fs_temp_nonefix a,fs_temp_fix b
where ISNUMERIC(a.LINKMAN_PHONE)=1 and a.LINKMAN_PHONE =b.LINKMAN_PHONE and a.SOCIAL_ID <>b.SOCIAL_ID
and a.serv_id not in(select fix2.serv_id from fs_temp_fix_1 fix2)--------将结果追加到[fs_temp_fix]表
insert into [fs_temp_fix] select * from [fs_temp_fix_1]
end
我用的是上面这个,
楼上的select a.*,b.groupid,2,0,0 ,这里这个2是a.LINKMAN_PHONE =b.LINKMAN_PHONE时候为2,而a.SOCIAL_ID =b.SOCIAL_ID的时候则为1哦
我对索引不熟悉,不太会哦,请指教
但是,你又是在fs_temp_fix表插入数据,即查询又插入,建立索引后,查询加块,插入减慢,确实比较矛盾,你自己权衡吧.
MERGE INTO --的用法