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
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
(SELECT bomid FROM A WHERE bomid NOT IN (SELECT bomid FROM B))AS B
CROSS JOIN (SELECT fileid FROM B) AS T
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,30select 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 行受影响)
*/
select a.bomid,k.fileid
from A,(select fileid from b) k
where not exists(select * from b where a.bomid=bomid)
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,30INSERT B SELECT * FROM
(SELECT bomid FROM A WHERE bomid NOT IN (SELECT bomid FROM B))AS B
CROSS JOIN (SELECT fileid FROM B) AS TSELECT * FROM Bbomid 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 1002select * from @A表declare @b表 table (bomid int,fileid int)
insert into @b表
select 1000,10 union all
select 1000,20 union all
select 1000,30insert 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
*/
-- 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 行受影响)
*/