现在有2个数据库挂在同一个服务器上,2个数据库结构相同,现在要求把2个数据库合并。。
合并后,数据库中的表,效果如下所示:
db1.dbo.table1 主键为 id 内有数据
id content
1 tt
2 yydb2.dbo.table1 主键为 id 内有数据
id content
1 bc
2 jj
3 kk
直接倒入会出现 因为 ID相同而出错,要得效果是
db1.dbo.table1 主键为 id
id content
1 tt
2 yy
3 bc
4 jj
5 kk 如果ID允许修改,那怎么自动修改ID ,并导入数据如果ID不允许修改,又怎样导入
表3(ID int identity(1,1),content varchar(20))
然后再把两个表数据导入表3
insert into table3 select content from (
select content from table1
union all
select content from table2
) A
(
select [content] from db1.dbo.table1
union all
select [content] from db2.dbo.table1
)
--先设置表中id可以手工插入:SET IDENTITY_INSERT 表名 ON
--然后
use db1
goCREATE FUNCTION f_NextBH()
RETURNS char(6)
AS
BEGIN
declare @id char(6)
select @id=isnull(max(id),0)+1 from table1
return @id
END
GO
insert into table1 select dbo.f_nextbh(),content from db2..table1select * from table1
按照你的思路,table3中插入的数据为:
1 tt
2 yy
1 bc
2 jj
3 kk
但是 因为table3 中 id为主键,所以为出错
select content
from db2.dbo.table1
order by id如果id不是自增长字段insert db1.dbo.table1 (id,content )
select id=(select max(id) from db1.dbo.table1)+id,
content
from db2.dbo.table1
order by id
---测试
create table tba(id int,content varchar(10))
insert into tba select 1,'tt'
insert into tba select 1,'YY'
go
create table tbb (id int,content varchar(10))
insert into tbb select 1,'bc'
insert into tbb select 1,'jj'
insert into tbb select 1,'kk'
go
create table tbc (id int identity(1,1),content varchar(10))insert into tbc select content from (
select content from tba
union all
select content from tbb
) Tselect * from tbc
/**
result
-----------------
id content
1 tt
2 YY
3 bc
4 jj
5 kk*/
drop table tba,tbb,tbc
insert into db1.dbo.table1(content)
select content from db2.dbo.table1 order by id asc
(
select [content] from db1.dbo.table1
union all
select [content] from db2.dbo.table1
)
/*test*/
if object_id('tempdb.dbo.#table1') is not null
drop table tempdb.dbo.#table1
create table #table1(id int identity(1,1), content nvarchar(10))
insert into #table1
select 'tt' union all
select 'YY' if object_id('tempdb.dbo.#table2') is not null
drop table tempdb.dbo.#table2
create table #table2(id int identity(1,1), content nvarchar(10))
insert into #table2
select 'bc' union all
select 'jj' union all
select 'kk'insert #table1 ( content )
select content
from #table2
order by idselect * from #table1
---------------------------------------------------------------------/*Output*/id content
----------- ----------
1 tt
2 YY
3 bc
4 jj
5 kk