关于两张表做同步的问题

[我是无聊至极的人]

如题有两张表（tb1，tb2）规定时间段做同步

[發糞塗牆]

做个JOB，周期性Insert，带上where条件，你的问题太虚了，一下子把问题无限扩大

[發糞塗牆]

两个表在同一个库？同一个架构？同一个实例？还是不同服务器？同一个SQL Server版本？结构是否一致？所谓的同步是全部清空再插入还是要保留历史信息？周期有多短？等等一系列问题，你不说清楚的话，别人就随便告诉你

[唐诗三百首]

可以用SQL Server的复制工具(replication)实现表与表的数据同步.

[--小F--]

同步复制？ 合并复制？快照复制？事务复制？

[黑暗中的光芒]

--不知道能不能满足你的需求
USE TEMPDB  
GO  
IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1  
IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2  
GO  
CREATE TABLE T1(ID1 INT,VAL1 VARCHAR(50))  
CREATE TABLE T2(ID2 INT,VAL2 VARCHAR(50))  
GO  
INSERT INTO T1  
SELECT 1,'A' UNION ALL  
SELECT 2,'B' UNION ALL  
SELECT 3,'C'  
  
MERGE INTO T2 AS TB_TARGET  
USING T1 AS TB_SOURCE  
ON TB_TARGET.ID2=TB_SOURCE.ID1  
WHEN NOT MATCHED BY TARGET THEN   
INSERT(ID2,VAL2)  
VALUES(ID1,VAL1)  
WHEN NOT MATCHED BY SOURCE THEN  
DELETE  
WHEN MATCHED AND TB_TARGET.VAL2<>TB_SOURCE.VAL1 THEN   
UPDATE SET  
TB_TARGET.VAL2=TB_SOURCE.VAL1  
OUTPUT $ACTION,ISNULL(DELETED.ID2,INSERTED.ID2) AS ID,DELETED.VAL2,INSERTED.VAL2  
;  

[我是无聊至极的人]

引用 5 楼 MSSQL_DBA 的回复:

--不知道能不能满足你的需求
USE TEMPDB  
GO  
IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1  
IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2  
GO  
CREATE TABLE T1(ID1 INT,VAL1 VARCHAR(50))  
CREATE TABLE T2(ID2 INT,VAL2 VARCHAR(50))  
GO  
INSERT INTO T1  
SELECT 1,'A' UNION ALL  
SELECT 2,'B' UNION ALL  
SELECT 3,'C'  
  
MERGE INTO T2 AS TB_TARGET  
USING T1 AS TB_SOURCE  
ON TB_TARGET.ID2=TB_SOURCE.ID1  
WHEN NOT MATCHED BY TARGET THEN   
INSERT(ID2,VAL2)  
VALUES(ID1,VAL1)  
WHEN NOT MATCHED BY SOURCE THEN  
DELETE  
WHEN MATCHED AND TB_TARGET.VAL2<>TB_SOURCE.VAL1 THEN   
UPDATE SET  
TB_TARGET.VAL2=TB_SOURCE.VAL1  
OUTPUT $ACTION,ISNULL(DELETED.ID2,INSERTED.ID2) AS ID,DELETED.VAL2,INSERTED.VAL2  
;  

非常感谢！