sqlserver数据库实现自动同步表字段表数据更新

1、我们可以先创建一个案例表。

--创建A表

create table table_A

(name varchar(20))

insert into table_A

select 'liu'

union

select 'zhang'

select * from table_A

2、通过复制表的方式，创建一个B表，输入脚本并执行。

select top 0 * into table_B from table_A

select * from table_B

3、同步实现的原理通过触发器进行实现，如果A表进行更新了，那么我们同时在B表进行表字段更新，同时进行B表的数据更新。

使用下一步的脚本，建立一个A表的触发器，注意表名，进行修改。

4、--创建一个A表的触发器

create trigger tr_table_a on table_a

for insert,update

as

begin

     ---同步table_a表的列

      declare @newcolname varchar(2000),@newtype varchar(2000),@newlength varchar(2000),

              @newprec varchar(2000),@newscale varchar(2000)

      declare @sql varchar(5000)

      set @sql = ''

      declare cur_col cursor for

  select B.name,C.name,B.length,B.prec,B.scale

    FROM  sysobjects  a  left join syscolumns b on a.id = b.id 

   left join systypes c on c.[xusertype] = b.[xusertype]

where a.name = 'table_a' and b.name not in (

                           select b.name as typename

                           FROM  sysobjects  a  left join syscolumns b on a.id = b.id 

           left join systypes c on c.[xusertype] = b.[xusertype]

                           where a.name = 'table_b'      --注意修改表名

                            )

      open cur_col

      fetch next from cur_col into @newcolname,@newtype,@newlength,@newprec,@newscale

  while @@FETCH_STATUS = 0

  begin

if @newtype = 'varchar' or @newtype = 'char' 

begin

   set @sql = 'alter table table_b add '+ @newcolname +' '+ @newtype + '('+@newlength+')'   --注意修改表名

end else

begin

   set  @sql = 'alter table table_b add '+ @newcolname +' '+ @newtype + '('+@newprec+','+@newscale+')'   --注意修改表名

end

     exec(@sql)

  fetch next from cur_col into @newcolname,@newtype,@newlength,@newprec,@newscale

  end

  close cur_col

  deallocate cur_col

      --- 同步表数据

      declare @name varchar(200) , @colid varchar(200)

      declare @str_colname varchar(4000)

      declare @sql_data varchar(5000)

      set @str_colname = ''

  select * into #tmp_inserted from inserted 

  declare cur cursor for

  select b.name,colid

  FROM  sysobjects  a  inner join syscolumns b

  on a.id = b.id

  where a.name =  'table_a'      --注意修改表名

  open cur

  fetch next from cur into @name,@colid

  while @@FETCH_STATUS = 0

  begin

     set  @str_colname = @str_colname  + @name +', '

     fetch next from cur into @name,@colid 

  end

  close cur

  deallocate cur

  set @str_colname = left(@str_colname,len(@str_colname)-1)

  print @str_colname

  set @sql_data = '

  insert into table_B ('+@str_colname+')   

  select  '+ @str_colname + ' from temp..#tmp_inserted'

  exec(@sql_data)

end

5、建立好触发器，我们来测试给A表增加列，同时也给A表增加数据。

看到B表中，是不是自动完成列的增加，数据的增加

--测试给A表增加字段address

alter table table_a

add address varchar(80)

alter table table_a

add personnum numeric(12,2)

6、给A表写入数据

---测试给A表增加数据

insert into table_A (name,address,personnum)

select 'tbc','yi huan 101',40000200

7、查看两个表的数据，验证触发器已经生效。 可以发现列已经增加完毕，数据也同步增加了。

---检查两个表数据

select * from Table_A

select * from table_B

8、以下是测试完整的代码：

--创建A表

create table table_A

(name varchar(20))

insert into table_A

select 'liu'

union

select 'zhang'

select * from table_A

---复制的方式创建B表

select top 0 * into table_B from table_A

select * from table_B

--创建一个A表的触发器

create trigger tr_table_a on table_a

for insert,update

as

begin

     ---同步table_a表的列

      declare @newcolname varchar(2000),@newtype varchar(2000),@newlength varchar(2000),

              @newprec varchar(2000),@newscale varchar(2000)

      declare @sql varchar(5000)

      set @sql = ''

      declare cur_col cursor for

  select B.name,C.name,B.length,B.prec,B.scale

    FROM  sysobjects  a  left join syscolumns b on a.id = b.id 

   left join systypes c on c.[xusertype] = b.[xusertype]

where a.name = 'table_a' and b.name not in (

                           select b.name as typename

                           FROM  sysobjects  a  left join syscolumns b on a.id = b.id 

           left join systypes c on c.[xusertype] = b.[xusertype]

                           where a.name = 'table_b'      --注意修改表名

                            )

      open cur_col

      fetch next from cur_col into @newcolname,@newtype,@newlength,@newprec,@newscale

  while @@FETCH_STATUS = 0

  begin

if @newtype = 'varchar' or @newtype = 'char' 

begin

   set @sql = 'alter table table_b add '+ @newcolname +' '+ @newtype + '('+@newlength+')'   --注意修改表名

end else

begin

   set  @sql = 'alter table table_b add '+ @newcolname +' '+ @newtype + '('+@newprec+','+@newscale+')'   --注意修改表名

end

     exec(@sql)

  fetch next from cur_col into @newcolname,@newtype,@newlength,@newprec,@newscale

  end

  close cur_col

  deallocate cur_col

      --- 同步表数据

      declare @name varchar(200) , @colid varchar(200)

      declare @str_colname varchar(4000)

      declare @sql_data varchar(5000)

      set @str_colname = ''

  select * into #tmp_inserted from inserted 

  declare cur cursor for

  select b.name,colid

  FROM  sysobjects  a  inner join syscolumns b

  on a.id = b.id

  where a.name =  'table_a'      --注意修改表名

  open cur

  fetch next from cur into @name,@colid

  while @@FETCH_STATUS = 0

  begin

     set  @str_colname = @str_colname  + @name +', '

     fetch next from cur into @name,@colid 

  end

  close cur

  deallocate cur

  set @str_colname = left(@str_colname,len(@str_colname)-1)

  print @str_colname

  set @sql_data = '

  insert into table_B ('+@str_colname+')   

  select  '+ @str_colname + ' from temp..#tmp_inserted'

  exec(@sql_data)

end

---测试语句

--测试给A表增加字段address

alter table table_a

add address varchar(80)

alter table table_a

add personnum numeric(12,2)

---测试给A表增加数据

insert into table_A (name,address,personnum)

select 'tbc','yi huan 101',40000200

---检查两个表数据

select * from Table_A

select * from table_B