CREATE PROCEDURE TEST() AS BEGIN DELETE FROM Cus_B WHERE EXISTS(SELECT 1 FROM Cus_A WHERE Cus_B .ID=Cus_A .ID)INSERT INTO CUS_B SELECT * FROM CUS_A END
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) end go
insert into Cus_B(ID*,Name,Address) select ID*,Name,Address from Cus_A a where Cus_B.id in (select id from a where id in(select id from Cus_B))
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
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 sp_test as truncate table Cus_B ; insert Cus_B select * from Cus_A 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 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 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)
不能用TRUNCATE,两个表各有对方不存在的ID
--> 测试数据:[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 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
如果A是123,B是234,TRUNCATE就把1删了,再插234就掉数据了
CREATE PROCEDURE TEST() AS BEGINDELETE Cus_B FROM Cus_A WHERE Cus_B.ID=Cus_A.IDINSERT INTO CUS_B SELECT * FROM CUS_AEND
CREATE TABLE Cus_A(Id INT, Name VARCHAR(15), Address VARCHAR(30)) INSERT INTO Cus_A(Id, Name, Address) SELECT 1, '李艳', '湖南衡阳' UNION ALL SELECT 2, '胡钟平', '广西南宁' UNION ALL SELECT 3, '江也', '河北沧州' UNION ALL SELECT 4, '周鑫', '山东济南' UNION ALL SELECT 8, '武涛', '湖北武汉' UNION ALL SELECT 9, '刘军', '北京朝阳' UNION ALL SELECT 10, '郑丽', '河南郑州' UNION ALL SELECT 11, '钟娟', '江西九江';CREATE TABLE Cus_B(ID INT, Name VARCHAR(15), Address VARCHAR(30)) INSERT INTO Cus_B(Id, Name, Address) SELECT 3, '武松', '湖南张家界' UNION ALL SELECT 5, '宋江', '北京昌平' UNION ALL SELECT 10, '李广元', '山东济南' UNION ALL SELECT 12, '孝凯', '辽宁沈阳';SELECT * FROM Cus_A; SELECT * FROM Cus_B;CREATE PROCEDURE test_proc AS BEGININSERT INTO Cus_B SELECT * FROM Cus_A a WHERE NOT EXISTS ( SELECT * FROM Cus_B b WHERE b.Id=a.Id );UPDATE Cus_B SET Name=a.Name, Address=a.Address FROM Cus_A a, Cus_B b WHERE b.Id=a.Id;ENDEXEC test_proc;SELECT * FROM Cus_A; SELECT * FROM Cus_B; SELECT * FROM Cus_B b WHERE EXISTS ( SELECT * FROM Cus_A a WHERE a.Id=b.Id );DROP TABLE Cus_A; DROP TABLE Cus_B; DROP PROCEDURE test_proc;
AS
BEGIN
DELETE FROM Cus_B
WHERE EXISTS(SELECT 1 FROM Cus_A WHERE Cus_B .ID=Cus_A .ID)INSERT INTO CUS_B
SELECT * FROM CUS_A
END
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
Cus_B(ID*,Name,Address)
select
ID*,Name,Address from Cus_A a
where
Cus_B.id
in
(select id from a where id in(select id from Cus_B))
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
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
as
truncate table Cus_B ;
insert Cus_B select * from Cus_A
go
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 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 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)
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 行受影响)
*/
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
AS
BEGINDELETE Cus_B
FROM Cus_A
WHERE Cus_B.ID=Cus_A.IDINSERT INTO CUS_B
SELECT * FROM CUS_AEND
INSERT INTO Cus_A(Id, Name, Address)
SELECT 1, '李艳', '湖南衡阳' UNION ALL
SELECT 2, '胡钟平', '广西南宁' UNION ALL
SELECT 3, '江也', '河北沧州' UNION ALL
SELECT 4, '周鑫', '山东济南' UNION ALL
SELECT 8, '武涛', '湖北武汉' UNION ALL
SELECT 9, '刘军', '北京朝阳' UNION ALL
SELECT 10, '郑丽', '河南郑州' UNION ALL
SELECT 11, '钟娟', '江西九江';CREATE TABLE Cus_B(ID INT, Name VARCHAR(15), Address VARCHAR(30))
INSERT INTO Cus_B(Id, Name, Address)
SELECT 3, '武松', '湖南张家界' UNION ALL
SELECT 5, '宋江', '北京昌平' UNION ALL
SELECT 10, '李广元', '山东济南' UNION ALL
SELECT 12, '孝凯', '辽宁沈阳';SELECT * FROM Cus_A;
SELECT * FROM Cus_B;CREATE PROCEDURE test_proc
AS
BEGININSERT INTO Cus_B
SELECT * FROM Cus_A a
WHERE NOT EXISTS ( SELECT * FROM Cus_B b WHERE b.Id=a.Id );UPDATE Cus_B
SET Name=a.Name, Address=a.Address
FROM Cus_A a, Cus_B b
WHERE b.Id=a.Id;ENDEXEC test_proc;SELECT * FROM Cus_A;
SELECT * FROM Cus_B;
SELECT * FROM Cus_B b
WHERE EXISTS ( SELECT * FROM Cus_A a WHERE a.Id=b.Id );DROP TABLE Cus_A;
DROP TABLE Cus_B;
DROP PROCEDURE test_proc;