如何根据对应的表批量插入

[xuxugr]

A表
bomid
1000
1001
1002


b表
bomid fileid
1000 10
1000 20
1000 30

我想在b表中插入其余的bomid及和bomid一样的fileid,结果是
B表
1000 10
1000 20
1000 30
1001 10
1001 20
1001 30
1002 10
1002 20
1002 30

[SQL77]

INSERT B SELECT * FROM 
(SELECT bomid  FROM A WHERE bomid  NOT IN (SELECT bomid  FROM B))AS B
CROSS JOIN (SELECT fileid  FROM B) AS T

[百年树人]

select a.bomid,b.fileid from a,b

[百年树人]

if object_id('[A]') is not null drop table [A]
go
create table [A]([bomid] int)
insert [A]
select 1000 union all
select 1001 union all
select 1002
if object_id('[B]') is not null drop table [B]
go
create table [B]([bomid] int,[fileid] int)
insert [B]
select 1000,10 union all
select 1000,20 union all
select 1000,30

select a.bomid,b.fileid from a,b 
order by bomid,fileid

--测试结果:
/*
bomid       fileid
----------- -----------
1000        10
1000        20
1000        30
1001        10
1001        20
1001        30
1002        10
1002        20
1002        30

(9 行受影响)
*/

[feixianxxx]

insert b
select a.bomid,k.fileid
from A,(select fileid from b) k
where not exists(select * from b where a.bomid=bomid)

[SQL77]

if object_id('[A]') is not null drop table [A]
go
create table [A]([bomid] int)
insert [A]
select 1000 union all
select 1001 union all
select 1002
if object_id('[B]') is not null drop table [B]
go
create table [B]([bomid] int,[fileid] int)
insert [B]
select 1000,10 union all
select 1000,20 union all
select 1000,30

INSERT B SELECT * FROM 
(SELECT bomid  FROM A WHERE bomid  NOT IN (SELECT bomid  FROM B))AS B
CROSS JOIN (SELECT fileid  FROM B) AS T

SELECT * FROM B

bomid       fileid      
----------- ----------- 
1000        10
1000        20
1000        30
1001        10
1001        20
1001        30
1002        10
1002        20
1002        30

（所影响的行数为 9 行）

借树哥数据了,插入

[叶子]

declare @A表 table (bomid int)
insert into @A表
select 1000 union all
select 1001 union all
select 1002

select * from @A表

declare @b表 table (bomid int,fileid int)
insert into @b表
select 1000,10 union all
select 1000,20 union all
select 1000,30

insert into @b表
select a.bomid,b.fileid from @b表 b CROSS JOIN @A表 a 

select distinct * from @b表
/*
bomid       fileid
----------- -----------
1000        10
1000        20
1000        30
1001        10
1001        20
1001        30
1002        10
1002        20
1002        30
*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-10-21 09:53:06
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([bomid] int)
insert [A]
select 1000 union all
select 1001 union all
select 1002
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([bomid] int,[fileid] int)
insert [b]
select 1000,10 union all
select 1000,20 union all
select 1000,30
--------------开始查询--------------------------
insert 
  b
select 
  a.bomid,b.fileid
from 
  a
cross join
 (select fileid from b)b
where 
  not exists(select * from b where a.bomid=bomid)


select * from b
----------------结果----------------------------
/*bomid       fileid
----------- -----------
1000        10
1000        20
1000        30
1001        10
1001        20
1001        30
1002        10
1002        20
1002        30

(9 行受影响)
 
*/

[46539492]

不错，Cross join