两张表同步

shoushii 2010-09-13 04:26:57

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_2

DROP TABLE TABLE_1
DROP TABLE TABLE_2
/* 要求A表结果是:
1 Lei Chi
2 Alex Chi
*/


注:
1.第二个表中的删除,增加和修改都应该同步到第一个表中
2.没有更新的列(值相同),不要更新。
...全文
159 22 打赏 收藏 转发到动态 举报
写回复
用AI写文章
22 条回复
切换为时间正序
请发表友善的回复…
发表回复
shoushii 2010-09-15
  • 打赏
  • 举报
回复
虽然没有满意答案,但为了返回的50分,还是给了最积极的人。
shoushii 2010-09-14
  • 打赏
  • 举报
回复
高手来呀
shoushii 2010-09-14
  • 打赏
  • 举报
回复

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_2

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.*;

SET NOCOUNT ON
SELECT * FROM Table_1
SELECT * FROM Table_2

SET 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)
shoushii 2010-09-14
  • 打赏
  • 举报
回复
[Quote=引用 19 楼 xlh0053 的回复:]
表2不能被修改,没有权限与第二个表中的删除,增加和修改都应该同步到第一个表中这两个条件不是自相矛盾么?
[/Quote]
第二个表是别的系统的,我没有修改权限,别人会有。
我可以查询第二个表,当然就可以同步了。
xlh0053 2010-09-14
  • 打赏
  • 举报
回复
表2不能被修改,没有权限第二个表中的删除,增加和修改都应该同步到第一个表中这两个条件不是自相矛盾么?
shoushii 2010-09-13
  • 打赏
  • 举报
回复
睡前一顶~~~
哥子谭 2010-09-13
  • 打赏
  • 举报
回复
貌似使用触发器可以达到同步的目的
hovy_yang 2010-09-13
  • 打赏
  • 举报
回复
等待、学习、关注
shoushii 2010-09-13
  • 打赏
  • 举报
回复

~~~顶~~~
shoushii 2010-09-13
  • 打赏
  • 举报
回复
[Quote=引用 12 楼 ws_hgo 的回复:]
引用 11 楼 shoushii 的回复:
SQL code

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.Name……
[/Quote]
值需要在更新的时候,判断column的值是否变化,否则不更新。插入和删除时内置的。


期待继续赐教
ws_hgo 2010-09-13
  • 打赏
  • 举报
回复
[Quote=引用 11 楼 shoushii 的回复:]
SQL code

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 = S……
[/Quote]

这样你写2个存储过程
(1)当插入数据到表2的时候 判断条件之后插入表1
(2)当删除表2数据的时候,根据条件查找然后删除表1对应的信息
shoushii 2010-09-13
  • 打赏
  • 举报
回复

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.*;


这个例子只是没有选择性的更新
ws_hgo 2010-09-13
  • 打赏
  • 举报
回复
[Quote=引用 9 楼 shoushii 的回复:]
引用 6 楼 ws_hgo 的回复:
SQL code
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……
[/Quote]

不用触发器
此题无解

上面的我那个包含插入,更新,删除
shoushii 2010-09-13
  • 打赏
  • 举报
回复
[Quote=引用 6 楼 ws_hgo 的回复:]
SQL code
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 dr……
[/Quote]
1.不能用触发器
2.Name2没有更新,不需要update。
ws_hgo 2010-09-13
  • 打赏
  • 举报
回复
修改下
完整版本


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_1

ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi
3 huguo Chi

(3 行受影响)

--删除测试
delete from Table_2 where ID=3

ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi

(2 行受影响)
--更新测试

select * from Table_2

update Table_2 set Name1='ALex2' where ID=2

ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 ALex2 Chi

(2 行受影响)
shoushii 2010-09-13
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 lds1ove 的回复:]
触发器?????????
[/Quote]
没有对B表的修改权限,不能用触发器
ws_hgo 2010-09-13
  • 打赏
  • 举报
回复
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,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_1

ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi
3 huguo Chi

(3 行受影响)


delete from Table_2 where ID=3

ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi

(2 行受影响)
lds1ove 2010-09-13
  • 打赏
  • 举报
回复
触发器?????????
shoushii 2010-09-13
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 aaron_gl 的回复:]
触发器
[/Quote]
表2不能被修改,没有权限
「已注销」 2010-09-13
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 hao1hao2hao3 的回复:]

哪来的A表?
[/Quote]

就是Table_1表
加载更多回复(2)

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧