从两个数据库中提取数据组成一个新表,急!!!
请问着个怎么实现?
[解决办法]
select * from db1..a union all select * from db2..b
[解决办法]
用服務器名.數據庫名.所有者.對象名
[解决办法]
insert into newtable
select * from 数据库名a..表名
union all
select * from 数据库名b..表名
[解决办法]
--视图
select *
from database1..table1,database2..table2
where ... (条件)
--生成新表
select *
into table3
from database1..table1,database2..table2
where ... (条件)
[解决办法]
数据是同一台服务的处理方式
select * into #newtable from aa..tablea a inner join bb..tableb b where a.id=b.id
如果是不同服务器,建立链接服务器,其他的操作同上一样
[解决办法]
create table T1(ID1 int, Name1 varchar(10), Age1 int)
insert T1 select 11, 'Table11 ',111
union all select 12, 'Table12 ',112
union all select 13, 'Table13 ',113
create table T2(ID2 int, Name2 varchar(10), Age2 int)
insert T2 select 21, 'Table21 ',221
union all select 22, 'Table22 ',222
union all select 23, 'Table23 ',223
select ID=identity(int, 1, 1), * into #T1 from T1
select ID=identity(int, 1, 1), * into #T2 from T2
select * from #T1 A
full join #T2 B on A.ID=B.ID
--result
ID ID1 Name1 Age1 ID ID2 Name2 Age2
----------- ----------- ---------- ----------- ----------- ----------- ---------- -----------
1 11 Table11 111 1 21 Table21 221
2 12 Table12 112 2 22 Table22 222
3 13 Table13 113 3 23 Table23 223
(3 row(s) affected)
drop table #T1, #T2
[解决办法]
--------------data
create table table1(ID1 int, Name1 varchar(10), Age1 int)
insert table1 select 11, 'Table11 ',111
union all select 12, 'Table12 ',112
union all select 13, 'Table13 ',113
create table table2(ID2 int, Name2 varchar(10), Age2 int)
insert table2 select 21, 'Table21 ',221
union all select 22, 'Table22 ',222
union all select 23, 'Table23 ',223
--------------------SQL
select ID_1=identity(int, 1, 1), * into #temp1 from table1
select ID_2=identity(int, 1, 1), * into #temp2 from table2
select * from #temp1 as A
full join #temp2 as B on A.[ID_1]=B.[ID_2]
drop table #temp1
drop table #temp2
drop table table1
drop table table2
--------------------Result
ID_1ID1Name1Age1ID_2ID2Name2Age2
111Table11111121Table21221
212Table12112222Table22222
313Table13113323Table23223
(3 件処理されました)
[解决办法]
基本原理是一样的
之需要在相应的表明前面加数据库名就可以了
[解决办法]
MyTest和pubs是两个数据库
use MyTest
go
create table table1(ID1 int, Name1 varchar(10), Age1 int)
insert table1 select 11, 'Table11 ',111
union all select 12, 'Table12 ',112
union all select 13, 'Table13 ',113
use pubs
go
create table table2(ID2 int, Name2 varchar(10), Age2 int)
insert table2 select 21, 'Table21 ',221
union all select 22, 'Table22 ',222
union all select 23, 'Table23 ',223
use MyTest
select ID_1=identity(int, 1, 1), * into #temp1 from MyTest.dbo.table1
select ID_2=identity(int, 1, 1), * into #temp2 from pubs.dbo.table2
select * from #temp1 as A
full join #temp2 as B on A.[ID_1]=B.[ID_2]
drop table #temp1
drop table #temp2
drop table MyTest.dbo.table1
drop table pubs.dbo.table2
