/*----------------------------------------------------------------
-- Author :feixianxxx(poofly)
-- Date :2010-04-29 14:45:30
-- Version:
-- Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: )
-- Content:同步2个表的内容
----------------------------------------------------------------*/
说明:使2个表同步的方法有很多，其中有一种利用触发器进行同步，
在SQL Server2008里可以使用Merge进行同步(http://blog.csdn.net/feixianxxx/archive/2010/02/07/5296519.aspx)
这里介绍一种利用触发器同步的方法,特别适用于表的字段非常多缺不想列出全部列，只列出更新的列的情况。
PS：不知道UPDATE表的时候，set 后面的列越多是不是越耗资源，如果消耗几乎没变 那此脚本意义就不大了。希望大大们帮忙解惑
--环境：
--源表
IF OBJECT_ID('dbo.Sources') IS NOT NULL
DROP TABLE dbo.Sources;
GO
DECLARE @cmd AS NVARCHAR(4000), @i AS INT;
SET @cmd =
N'CREATE TABLE dbo.Sources(keycol INT NOT NULL PRIMARY KEY';
SET @i = 1;
WHILE @i <= 100
BEGIN
SET @cmd =
@cmd + N',col' + RTRIM(@i) +
N' INT NOT NULL DEFAULT 0';
SET @i = @i + 1;
END
SET @cmd = @cmd + N');'
EXEC sp_executesql @cmd;
go
--目标表(表结构和源表相同，字段名字不同)
IF OBJECT_ID('dbo.Target') IS NOT NULL
DROP TABLE dbo.Target;
GO
DECLARE @cmd AS NVARCHAR(4000), @i AS INT;
SET @cmd =
N'CREATE TABLE dbo.Target(keycol INT NOT NULL PRIMARY KEY';
SET @i = 1;
WHILE @i <= 100
BEGIN
SET @cmd =
@cmd + N',col_t' + RTRIM(@i) +
N' INT NOT NULL DEFAULT 0';
SET @i = @i + 1;
END
SET @cmd = @cmd + N');'
EXEC sp_executesql @cmd;
go
--建立触发器
create TRIGGER trTongbu_SourcesToTarget on dbo.Sources
for insert,update,delete
as
begin
if exists(select * from deleted )
begin
if exists(select * from inserted)
begin
DECLARE @i AS INT, @numcols AS INT;
DECLARE @s varchar(8000)
set @s=''
SET @numcols =(SELECT COUNT(*) from syscolumns where id=object_id('Sources'))
SET @i = 1;
--找出更新的列
WHILE @i <= @numcols
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@i - 1) / 8 + 1, 1) & POWER(2, (@i - 1) % 8) > 0)
begin
set @s=@s+','+(select name from syscolumns where colid = @i and id=object_id('Target'))+' = i.'+
(select name from syscolumns where colid = @i and id=object_id('Sources'))
end
SET @i = @i + 1;
END
--先将INSERTED表的内容导入另外一个表，因为动态语法不识别INSERTED表
select * into #test from inserted
--只set 更新的列
set @s='update Target set '+stuff(@s,1,1,'')+' from #test i where i.keycol=Target.keycol'
exec (@s);
end
else
begin
delete dbo.Target
from deleted d
where dbo.Target.keycol=d.keycol
end
end
else
begin
insert dbo.Target
select * from inserted
end
end
go
--测试：
--插入：
INSERT INTO dbo.Sources(keycol) values(1)
INSERT INTO dbo.Sources(keycol) values(2)
select * from dbo.target
--删除:
delete dbo.Sources where keycol=1
select * from dbo.target
--更新
update dbo.Sources set col2=1,col9=10,col12=19,col87=99 where keycol=2
select * from dbo.target
希望指正错误，并且讨论下 2表同步最好的办法
------解决方案--------------------
sf.
------解决方案--------------------
sf....
------解决方案--------------------
学习...
------解决方案--------------------
学习,接分.
------解决方案--------------------
..........UP
------解决方案--------------------
BD.