最近数据库升级到2008，研究了一下，发现有一个很好用的新功能，merge 语句加上存储过程，可以实现数据的差异传输，源表的增删改，都可以使用 merge 语句，把数据更改应用到目标表上，我们原来一个2亿多条记录的表，完全传输一次需要3个多小时，使用这个方法进行数据传输，首次花的时间和原来差不多，但是在以后的每次的作业里面，看到传输时间只有不到 10分钟了，经大量数据抽样检查，数据是完全相同的，通过优化，还可以增加作业的频率，实现数据更短的时间差，这对于表比较大，而且需要进行不定期同步的情况来说，节省了不少时间，当然，数据库镜像 和 数据发布订阅也能解决这个问题，但是对应用比较简单，数据同步实时性要求不到，数据量巨大的情况，这个方法部署起来还是相对简单一些的，呵呵
这个方法非常实用，发出来让大家分享一下，也欢迎各位大侠提出更好的解决办法
1：先在目标服务器上建立一个到源服务器的链接服务器，名字 xx2008（比较简单，不详细说了）
2：在表里面，增加一个字段 updatetime，记录数据更新时间，同时表里面要有 id 自动增加字段
3：以原库名 c_001 ， t_001 表为例，以在目标服务器上建立存储过程(这是例程，要提高效率的话，还可以优化过程)
4：建立作业，定时调用存储过程
-
SQL code
-
Create PROCEDURE [ dbo ] . [ backup_001 ] AS BEGIN SET NOCOUNT ON ; SET IDENTITY_Insert t_001 ON merge t_001 d using ( SELECT * FROM OPENQUERY (xx2008 , ' select * from c_001.dbo.t_001 ' ) ) s on (d.id = s.id and d.updatetime <> s.updatetime) WHEN NOT MATCHED BY SOURCE THEN delete when matched then update set d.f1 = s.f1, d.f2 = s.f2, d.f3 = s.f3, ...... d.updatetime = s.updatetime when not matched then insert (d.f1,d.f2,d.f3,......d.updatetime) values (s.f1,s.f2,s.f3,......s.updatetime); SET IDENTITY_Insert t_001 OFF END
我还有一个问题，如何通过参数，让这个存储过程能适应所有的表，现在这个方法还是要根据每个表的不同，需要单独建立存储过程，
这点比较繁复，有没有更好的方法，能使用在所有的表上呢，解决的另外还有高分相赠
SQL code
--
merge主要用于两表之间的关联操作 有两张结构一致的表：test1,test2
create
table
test1
(id
int
,name
varchar
(
20
))
go
create
table
test2
(id
int
,name
varchar
(
20
))
go
insert
into
test1(id,name)
values
(
1
,
'
boyi55
'
),(
2
,
'
51cto
'
),(
3
,
'
bbs
'
),(
4
,
'
fengjicai
'
),(
5
,
'
alis
'
)
insert
into
test2(id,name)
values
(
1
,
'
boyi
'
),(
2
,
'
51cto
'
)
merge test2 t
--
要更新的目标表
using test1 s
--
源表
on
t.id
=
s.id
--
更新条件（即主键）
when
matched
--
如果主键匹配，更新
then
update
set
t.name
=
s.name
when
not
matched
then
insert
values
(id,name);
--
目标主未知主键，插入。此语句必须以分号结束
select
a.id,a.name
as
name_1,b.name
as
name_2
from
test1
as
a,test2
as
b
where
a.id
=
b.id
/*
id name_1 name_2
----------- -------------------- --------------------
1 boyi55 boyi55
2 51cto 51cto
3 bbs bbs
4 fengjicai fengjicai
5 alis alis
*/
SQL code
IF OBJECT_ID (N ' dbo.Departments ' , N ' U ' ) IS NOT NULL DROP TABLE dbo.Departments; GO CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY , DeptName nvarchar ( 30 ), Manager nvarchar ( 50 )); GO INSERT INTO dbo.Departments VALUES ( 1 , ' Human Resources ' , ' Margheim ' ),( 2 , ' Sales ' , ' Byham ' ), ( 3 , ' Finance ' , ' Gill ' ),( 4 , ' Purchasing ' , ' Barber ' ), ( 5 , ' Manufacturing ' , ' Brewer ' ); GO SELECT * FROM dbo.Departments GO IF OBJECT_ID (N ' dbo.Departments_delta ' , N ' U ' ) IS NOT NULL DROP TABLE dbo.Departments_delta; GO CREATE TABLE dbo.Departments_delta (DeptID tinyint NOT NULL PRIMARY KEY , DeptName nvarchar ( 30 ), Manager nvarchar ( 50 )); GO INSERT INTO dbo.Departments_delta VALUES ( 1 , ' Human Resources ' , ' Margheim ' ), ( 2 , ' Sales ' , ' Erickson ' ), ( 3 , ' Accounting ' , ' Varkey ' ),( 4 , ' Purchasing ' , ' Barber ' ), ( 6 , ' Production ' , ' Jones ' ), ( 7 , ' Customer Relations ' , ' Smith ' ); GO SELECT * FROM dbo.Departments_delta GO MERGE dbo.Departments AS d USING dbo.Departments_delta AS dd ON (d.DeptID = dd.DeptID) WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName WHEN NOT MATCHED THEN INSERT (DeptID, DeptName, Manager) VALUES (dd.DeptID, dd.DeptName, dd.Manager) WHEN NOT MATCHED BY SOURCE THEN -- DELETE UPDATE SET d.Manager = '' , d.DeptName = '' OUTPUT $action, inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName, inserted.Manager AS SourceManager, deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName, deleted.Manager AS TargetManager; GO SELECT * FROM dbo.Departments GO
楼上的 Beirut ，好像你的也不能解决多个表的通用问题呢，有没有更好的办法能实现通用这个问题大家都可能遇到，实现的前提可能是先做5个存储分别实现各自table的Synchronous功能，然后抽取并合并为一个存储过程实现，最后进一步整合为通用存储（包含不断改进的过程）。
这里看似简单的问题搞复杂了，有朋友会这样理解。其实在生产库上没有任何事情是你能提前完全预料到的，哪怕一个小小的改动，可能会因不符合各种业务逻辑等等产生各种各样的问题。在大大小小的各类项目上线过程和调改过程中，这类问题已经司空见惯了。
存储过程看来是只能自己写，对设计实现没有什么意见，因为我也是在学习了解，没有实践过。
对于表的数据变更，也有其他的方式实现。比如
SQL code
扫一扫
976
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
