从两个表向一个表插入数据怎么做

Question

例如：Table1：ID1  Field1
             1    x
             2    y
             3    z
      Table2：ID2  Field2
             1    o
             2    p
             3    q
插入以后我想得到的是
      Table3：ID3  Field1 Field2
             1    x      o
             2    x      p
             3    x      q
             4    y      o
             5    y      p
             …… 
             9    z      q
就是取 Field1 Field2的所有组合,请给详细解答 谢谢       

Answer 1

insert into
  table3
select
  isnull(a.id1,b.id2) as id3,
  a.Field1,b.Field2
from
  table1 a full join table2 b
on
  a.id1=b.id2

Answer 2

create table Table1(ID1 int,Field1 varchar(10))
insert into Table1 select 1,'x'
insert into Table1 select 2,'y'
insert into Table1 select 3,'z'
create table Table2(ID2 int,Field2 varchar(10))
insert into Table2 select 1,'o'
insert into Table2 select 2,'p'
insert into Table2 select 3,'q'
go
select (a.id1-1)*3+b.id2 id,a.field1,b.field2 from Table1 a,table2 b
/*
id          field1     field2
----------- ---------- ----------
1           x          o
2           x          p
3           x          q
4           y          o
5           y          p
6           y          q
7           z          o
8           z          p
9           z          q(9 行受影响)*/
go
drop table table1,table2

Answer 3

if object_id('table1','U') is not null
   drop table table1
go
create table table1
(
 ID1 int identity(1,1),
 field1 varchar(1)
)
go
insert into table1 (field1)
select 'x' union 
select 'y' union 
select 'z'
go
if object_id('table2','U') is not null
   drop table table2
go
create table table2
(
 ID2 int identity(1,1),
 field2 varchar(1)
)
go
insert into table2(field2)
select 'o' union 
select 'p' union 
select 'q'
go
select ID3=identity(int,1,1),field1,field2 into table3 from table1 cross join table2
go
select * from table3
/*
ID3         field1 field2
----------- ------ ------
1           x      o
2           y      o
3           z      o
4           x      p
5           y      p
6           z      p
7           x      q
8           y      q
9           z      q
*/

Answer 4

select row_number()over(order by t1.ID1,t2.ID2) as ID3,t1.Field1,t2.Field2 from t1,t2