求一个存储过程.把一个数据库中所有信息导入到另一个数据库中.
database1 database2
table1 table1
table2 table2
tableSettingdatabase1和database2分别为两个数据库.database1中的表和database2中的表一摸一样.
database2中多一个tablesetting表,用于存放database1中所有数据表的名称.表结构为:
ID Name UpdateTime
1 table1 1900-1-1
2 table2 1900-1-1
database1的所有表中都有个UpdateTime和createtime字段.条件是:取出
SELECT * FROM database1中每个table
WHERE (UpdateTime > tableSetting的updatetime) AND (ID NOT IN
(SELECT id
FROM database1中每个table
WHERE createtime > tableSetting的updatetime))
的数据更新到
execute update database2中的相对应的表再取出
SELECT *
FROM database1中每个table
WHERE (createtime > tableSetting的updatetime)
的数据
execute insert database2中的相对应的表;最后把tablesetting中updatetime字段改成更新后的时间.
update tablesetting中updatetime不知道这么讲大家能不能理解我的意思.如果有不清楚的可以随时问我.下班时结贴.谢谢.
database1 database2
table1 table1
table2 table2
tableSettingdatabase1和database2分别为两个数据库.database1中的表和database2中的表一摸一样.
database2中多一个tablesetting表,用于存放database1中所有数据表的名称.表结构为:
ID Name UpdateTime
1 table1 1900-1-1
2 table2 1900-1-1
database1的所有表中都有个UpdateTime和createtime字段.条件是:取出
SELECT * FROM database1中每个table
WHERE (UpdateTime > tableSetting的updatetime) AND (ID NOT IN
(SELECT id
FROM database1中每个table
WHERE createtime > tableSetting的updatetime))
的数据更新到
execute update database2中的相对应的表再取出
SELECT *
FROM database1中每个table
WHERE (createtime > tableSetting的updatetime)
的数据
execute insert database2中的相对应的表;最后把tablesetting中updatetime字段改成更新后的时间.
update tablesetting中updatetime不知道这么讲大家能不能理解我的意思.如果有不清楚的可以随时问我.下班时结贴.谢谢.
sp_msforeachtable 'insert 操作 or (update 操作)',@whereand="and createtime >db2. tableSetting.updatetime"
table2 table2
table3 table3
tablesettingBase1和Base2数据库中的table1 table2 table3的数据表结构都是一样的.分别是Base1中table1
ID Name CreateTime UpdateTime
1 aa 2006-06-06 2007-07-07
2 bb 2006-05-05 1900-1-1 table2
ID Name CreateTime UpdateTime Status
1 aa 2006-06-06 2007-07-07 0
2 aa 2006-07-06 1900-07-07 1 table3
ID Name CreateTime UpdateTime Status
1 aa 2006-06-06 2007-07-07 0
2 aa 2006-07-06 1900-07-07 1
Base2中tablesetting表
ID Name UpdateTime
1 table1 1900-1-1
2 table2 1900-1-1
3 table3 1900-1-1
--試試這個,沒有測試啊~~~declare @name varchar(100)
declare @sql varchar(8000),@str varchar(4000)
declare c1 cursor for
select name from base1.dbo.sysobjects where xtype='u'
open c1
fetch next from c1 into @name
while @@fetch_status=0
begin
select @sql='',@str=''
select @str=@str+','+[name] from base1.dbo.syscolumns where object_id(@name)=id
and name not in ('CreateTime','UpdateTime')
select @str=stuff(@str,1,1,'')
select @sql='insert into base2.dbo.'+@name+'('+@str+') select '+@str+' from base1.dbo.'+@name
+' a where exists(select 1 from base2.dbo.tableSetting b where b.Name='''+@name
+''' and b.updatetime<a.UpdateTime) and a.id not in (select id from base1.dbo.'+@name
+' c where exists(select 1 from base2.dbo.tableSetting d where d.name='''+@name+''' and d.updatetime<c.createtime))' select @sql=@sql+' insert into base2.dbo.'+@name+'('+stuff(@str,1,1,'')+') select '+@str
+' from base1.dbo.'+@name+' e where exists(select 1 from base2.dbo.tableSetting f where f.name='''+@name
+''' and f.updatetime<e.createtime)'exec(@sql)
fetch next from c1 into @name
end
close c1
deallocate c1
declare @sql varchar(8000),@str varchar(4000)
declare c1 cursor for
select name from base1.dbo.sysobjects where xtype='u'
open c1
fetch next from c1 into @name
while @@fetch_status=0
begin
select @sql='',@str=''
select @str=@str+','+[name] from base1.dbo.syscolumns where object_id(@name)=id
and name not in ('CreateTime','UpdateTime')
select @str=stuff(@str,1,1,'')
select @sql='insert into base2.dbo.'+@name+'('+@str+') select '+@str+' from base1.dbo.'+@name
+' a where exists(select 1 from base2.dbo.tableSetting b where b.Name='''+@name
+''' and b.updatetime<a.UpdateTime) and a.id not in (select id from base1.dbo.'+@name
+' c where exists(select 1 from base2.dbo.tableSetting d where d.name='''+@name+''' and d.updatetime<c.createtime))' select @sql=@sql+' insert into base2.dbo.'+@name+'('+@str') select '+@str
+' from base1.dbo.'+@name+' e where exists(select 1 from base2.dbo.tableSetting f where f.name='''+@name
+''' and f.updatetime<e.createtime)'exec(@sql)
fetch next from c1 into @name
end
close c1
deallocate c1
declare @name varchar(100)
declare @sql varchar(8000),@str varchar(4000)
declare c1 cursor for
select name from base1.dbo.sysobjects where xtype='u'
open c1
fetch next from c1 into @name
while @@fetch_status=0
begin
select @sql='',@str=''
select @str=@str+','+[name] from base1.dbo.syscolumns where object_id(@name)=id
and name not in ('CreateTime','UpdateTime')
select @str=stuff(@str,1,1,'')
select @sql='insert into base2.dbo.'+@name+'('+@str+') select '+@str+' from base1.dbo.'+@name
+' a where exists(select 1 from base2.dbo.tableSetting b where b.Name='''+@name
+''' and b.updatetime<a.UpdateTime) and a.id not in (select id from base1.dbo.'+@name
+' c where exists(select 1 from base2.dbo.tableSetting d where d.name='''+@name+''' and d.updatetime<c.createtime))' select @sql=@sql+' insert into base2.dbo.'+@name+'('+@str+') select '+@str
+' from base1.dbo.'+@name+' e where exists(select 1 from base2.dbo.tableSetting f where f.name='''+@name
+''' and f.updatetime<e.createtime)'exec(@sql)
fetch next from c1 into @name
end
close c1
deallocate c1