CREATE TABLE TABLE_1
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)CREATE TABLE TABLE_2
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)INSERT INTO Table_1 VALUES(1,'adsd','Chi')
INSERT INTO Table_2 VALUES(1,'Lei','Chi')
INSERT INTO Table_2 VALUES(2,'Alex','Chi')SELECT * FROM Table_1
SELECT * FROM Table_2--在这里操作SELECT * FROM Table_1
SELECT * FROM Table_2DROP TABLE TABLE_1
DROP TABLE TABLE_2
/* 要求A表结果是:
1 Lei Chi
2 Alex Chi
*/
注:
1.第二个表中的删除,增加和修改都应该同步到第一个表中
2.没有更新的列(值相同),不要更新。
CREATE TABLE TABLE_1
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)
if object_id('TABLE_2') is not null drop table TABLE_2
CREATE TABLE TABLE_2
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)INSERT INTO Table_1 VALUES(1,'adsd','Chi')
INSERT INTO Table_2 VALUES(1,'Lei','Chi')
INSERT INTO Table_2 VALUES(2,'Alex','Chi')create trigger tr_info on TABLE_2
for insert,update,delete
as
begin
if exists(select 1 from inserted) and not exists(select 1 from deleted)
begin
insert into Table_1 select * from inserted
end
else if exists(select 1 from inserted) and exists(select 1 from deleted)
begin
update b1
set b1.Name1=U.Name1,b1.Name2=U.Name2
from Table_1 b1,updated U
where b1.ID=U.ID
end
else
begin
delete Table_1 where ID=(select ID from deleted)
end
end
INSERT INTO Table_2 VALUES(3,'huguo','Chi')
select * from TABLE_1ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi
3 huguo Chi (3 行受影响)
delete from Table_2 where ID=3ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi (2 行受影响)
完整版本
if object_id('TABLE_1') is not null drop table TABLE_1
CREATE TABLE TABLE_1
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)
if object_id('TABLE_2') is not null drop table TABLE_2
CREATE TABLE TABLE_2
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)INSERT INTO Table_1 VALUES(1,'adsd','Chi')
INSERT INTO Table_2 VALUES(1,'Lei','Chi')
INSERT INTO Table_2 VALUES(2,'Alex','Chi')create trigger tr_info on TABLE_2
for insert,update,delete
as
begin
if exists(select 1 from inserted) and not exists(select 1 from deleted)
begin
insert into Table_1 select * from inserted
end
else if exists(select 1 from inserted) and exists(select 1 from deleted)
begin
update b1
set b1.Name1=U.Name1,b1.Name2=U.Name2
from Table_1 b1,deleted U
where b1.ID=U.ID
end
else
begin
delete Table_1 where ID=(select ID from deleted)
end
end
--插入测试INSERT INTO Table_2 VALUES(3,'huguo','Chi')
select * from TABLE_1ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi
3 huguo Chi (3 行受影响)--删除测试
delete from Table_2 where ID=3ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi (2 行受影响)
--更新测试select * from Table_2update Table_2 set Name1='ALex2' where ID=2ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 ALex2 Chi (2 行受影响)
2.Name2没有更新,不需要update。
MERGE TABLE_1 AS Target
USING (SELECT ID, Name1, Name2 FROM Table_2) AS Source
ON (Target.ID = Source.ID)
WHEN MATCHED THEN
UPDATE SET Target.Name1 = Source.Name1, Target.Name2 = Source.Name2
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Name1, Name2) VALUES(ID, Name1, Name2)
WHEN NOT MATCHED BY Source THEN
DELETE
OUTPUT $action, Inserted.*, Deleted.*;
这个例子只是没有选择性的更新
期待继续赐教
CREATE TABLE TABLE_1
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)CREATE TABLE TABLE_2
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)INSERT INTO Table_1 VALUES(1,'adsd','Chi1');
INSERT INTO Table_1 VALUES(2,'adsd','Chi')
INSERT INTO Table_1 VALUES(3,'Jim','Green')
INSERT INTO Table_1 VALUES(4,'44','44')INSERT INTO Table_2 VALUES(2,'Alex','Chi')
INSERT INTO Table_2 VALUES(3,'Lei','Chi')
INSERT INTO Table_2 VALUES(9,'Fin','Chi')SET NOCOUNT ON
SELECT * FROM Table_1
SELECT * FROM Table_2MERGE TABLE_1 AS Target
USING (SELECT ID, Name1, Name2 FROM Table_2) AS Source
ON (Target.ID = Source.ID)
WHEN MATCHED THEN
UPDATE SET Target.Name1 = Source.Name1, Target.Name2 = Source.Name2
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Name1, Name2) VALUES(ID, Name1, Name2)
WHEN NOT MATCHED BY Source THEN
DELETE;
--OUTPUT $action, Inserted.*, Deleted.*; SET NOCOUNT ON
SELECT * FROM Table_1
SELECT * FROM Table_2SET NOCOUNT ON
DROP TABLE TABLE_1
DROP TABLE TABLE_2--DECLARE @ID INT
--DECLARE @Name1 NCHAR(10)
--DECLARE @Name2 NCHAR(10)--DECLARE mig CURSOR FOR
-- (SELECT * FROM Table_1)
--OPEN mig
--FETCH NEXT FROM mig INTO @ID,@Name1,@Name2
--WHILE @@FETCH_STATUS = 0
--BEGIN
-- FETCH NEXT FROM mig INTO @ID,@Name1,@Name2
--END
--CLOSE mig
--DEALLOCATE mig
--EXEC sp_estimate_data_compression_savings 'dbo', 'Table_1', NULL, NULL, 'ROW' ;--select * from Table_1 WHERE checksum(Name2) not in (select checksum(Name2) from Table_2) union all
--select * from Table_2 where checksum(Name2) not in (select checksum(Name2) from Table_1)