SQL2000下,有两个数据库,A,B,数据库基本结构一样(空数据时完全一致,有数据会产生新表)
A数据库里面的数据表不能通过导入导出来导出。但是可以用select * into B..tbname from A..tbname
能不能判断一下,把A库中包含数据的表转到B库,如果B库的的某表与要转移的表重名,则改为插入,或者替换掉。
如果有困难,直接把A库的所有用户表转到B库也行,同名改为插入或替换掉,需要保留主键。
A数据库里面的数据表不能通过导入导出来导出。但是可以用select * into B..tbname from A..tbname
能不能判断一下,把A库中包含数据的表转到B库,如果B库的的某表与要转移的表重名,则改为插入,或者替换掉。
如果有困难,直接把A库的所有用户表转到B库也行,同名改为插入或替换掉,需要保留主键。
(SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('B..TBNAME ') AND NAME='B..TBNAME')
DROP TABLE B..NAME
INSET B..tbname SELECT * from A..tbname
ELSE
select * into B..tbname from A..tbname ????
drop..
else
insert..
(SELECT 1 FROM SYSOBJECTS WHERE ID=OBJECT_ID('B..TBNAME ') AND NAME='B..TBNAME')
INSERT B..tbname SELECT * from A..tbname
ELSE select * into B..tbname from A..tbname
修改下
set @B='xinku'--新的数据库名称
declare @i int
set @i=0
declare @j int
select @j=count(name) from sysobjects where xtype='u' and name<>'dtproperties'
declare @a varchar(50)
declare 游标 scroll cursor
for
select name from sysobjects where xtype='u' and name<>'dtproperties'
open 游标
fetch first from 游标
declare @t table(id int identity(1,1),name varchar(50))
--下面的是数据库中的含有自增字段的表,可根据实际情况自己修改
insert into @t(name) select 'BiaoZhun' union select 'BiaoZhun1' union select 'CaiPinYongLiao' union
select 'CangKu' union select 'ChuKu' union select 'DaYinJiGuanLi' union select 'DengLuMingXi' union
select 'GongYingShang' union select 'JiaoBanJiLu' union select 'JiLiangDanWeiBiao' union select 'KuCun' union
select 'KuCunZhangHao' union select 'TaiWei' union select 'TuiCai' union select 'YuanCaiLiao' union
select 'zhuantaijilu' union select 'YuanCaiLiaoLeiBie' union select 'TuiCaiMingXi'
while @@FETCH_STATUS=0
begin
set @i=@i+1
if(@i>=@j)
break
fetch next from 游标 into @a
select * from @t where name =''+@a+''
if @@rowcount=0
begin
exec ('insert into '+@B+'..'+@a+' select * from '+@a)
end
else
begin
declare @lie varchar(200)
set @lie='bianhao'
--需要修改自增字段的
declare 游标1 scroll cursor
for
select name from syscolumns where id=object_id(''+@a+'')
open 游标1
declare @lie1 varchar(50)
fetch first from 游标1
while @@FETCH_STATUS=0
begin
fetch next from 游标1 into @lie1
set @lie=@lie+','+@lie1
end
set @lie=substring(@lie,1,len(@lie)-charindex(',', reverse(@lie)))
fetch first from 游标1
close 游标1
deallocate 游标1
exec ('set identity_insert '+@B+'..'+@a+' on insert into '+@B+'..'+@a+'('+@lie+') select * from '+@a+' set identity_insert '+@B+'..'+@a+' off')
end
end
close 游标
deallocate 游标参考一下
@command1 = N'IF OBJECT_ID(''A.?'') IS NOT NULL
INSERT INTO A.? SELECT * FROM ?
ELSE
SELECT * INTO A.? FROM ?'
关于sp_MsForeachTable 的一些用法
go
EXEC sp_MsForeachTable
@command1 = N'IF OBJECT_ID(''b.?'') IS NOT NULL
INSERT INTO b.? SELECT * FROM ?
ELSE
SELECT * INTO b.? FROM ?'
修改梁哥的。。
感觉应该反过来。。
导入是从A库到B库。。
(SELECT 1 FROM SYSOBJECTS WHERE NAME='B..TBNAME' AND TYPE='U')
INSERT B..tbname SELECT * from A..tbname
ELSE select * into B..tbname from A..tbname
drop table b..tbname
select * into b..tbname from a..tbname