22,300
社区成员




CREATE PROCEDURE TEST()
AS
BEGIN
DELETE Cus_B
FROM Cus_A
WHERE Cus_B.ID=Cus_A.ID
INSERT INTO CUS_B
SELECT * FROM CUS_A
END
create proc sp_test
as
--删除Cus_B表与Cus_A表id相同的记录
delete Cus_B where exists(select 1 from Cus_A where id=Cus_B.id);
--插入Cus_A表中的记录到Cus_B表中
insert Cus_B select * from Cus_A;
go
--> 测试数据:[Cus_A]
if object_id('[Cus_A]') is not null drop table [Cus_A]
go
create table [Cus_A]([ID] int,[Name] varchar(4),[Address] varchar(4))
insert [Cus_A]
select 1,'张三','河南' union all
select 2,'李四','洛阳' union all
select 3,'王五','北京' union all
select 4,'顺六','武汉' union all
select 5,'田七','上海'
select * from [Cus_A]
--> 测试数据:[Cus_B]
if object_id('[Cus_B]') is not null drop table [Cus_B]
go
create table [Cus_B]([ID] int,[Name] varchar(4),[Address] varchar(4))
insert [Cus_B]
select 1,'张三','河南' union all
select 2,'李四','洛阳' union all
select 3,'王五','北京' union all
select 6,'小二','重庆' union all
select 7,'王八','厦门'
-------------查询开始----------------
create proc pr_test
as
begin
insert into cus_b
select *
from cus_a a
where not exists(select 1 from cus_b where a.id = id)
update Cus_B
set name=a.name,address=a.address
from Cus_B b,Cus_A a
where a.id=b.id
end
go
exec pr_test
select * from [Cus_B]
----------------结果---------
/*
ID Name Address
----------- ---- -------
1 张三 河南
2 李四 洛阳
3 王五 北京
6 小二 重庆
7 王八 厦门
4 顺六 武汉
5 田七 上海
(7 行受影响)
*/
create proc pp
as
update Cus_B
set name=a.name,address=a.address
from Cus_B b,Cus_A a
where a.id=b.id
---可以先更新,再插入
insert Cus_B select * from Cus_A where id not in (select id from Cus_B)
汗 想错了
create proc f as
begin
insert into cus_b
select *
from cus_a a
where not exists(select 1 from cus_b where a.id = id)
end
go
create proc pr_test
as
begin
update b --还要更新,没注意到
set .....
from cus_b,cus_a
where a.id = b.id
insert into cus_b
select *
from cus_a a
where not exists(select 1 from cus_b where a.id = id)
end
go
create proc sp_test
as
truncate table Cus_B ;
insert Cus_B select * from Cus_A
go
create proc test
as
begin
delete cus_b where id in(select id from cus_a)
insert into cus_b
select * from cus_a
end
create proc pp
as
insert Cus_B select * from Cus_A where id not in (select id from Cus_B)
update Cus_B
set name=a.name,address=a.address
from Cus_B b,Cus_A a
where a.id=b.id