insert into b表 select t.id,401 as workid from a表 t where not exists(select 1 from b表 where id=t.id and workid=401)
insert b select id,401 from a where not exists(select 1 from b where workid = 401 and id = a.id)
--> 测试数据: @b declare @b table (id int,workid int) insert into @b select 1,301 union all select 1,401 union all select 2,301 union all select 2,201 union all select 3,102 union all select 3,202declare @a table (id int) insert into @a select 1 union all select 2 union all select 3 INSERT INTO @b SELECT * FROM (SELECT id,'401' as workid FROM @a) A WHERE not exists(select 1 from @b where id =A.id and workid =401)select * from @b /* id workid ----------- ----------- 1 301 1 401 2 301 2 201 3 102 3 202 2 401 3 401 */
--------------------------------- -- Author: htl258(Tony) -- Date : 2009-07-08 15:46:48 --------------------------------- --> 生成测试数据表-bif not object_id('b') is null drop table b Go Create table b([id] int,[workid] int) Insert b select 1,301 union all select 1,401 union all select 2,301 union all select 2,201 union all select 3,102 union all select 3,202 Go --Select * from b-->SQL查询如下: insert b select * from (select distinct id,401 [workid] from b where not exists(select 1 from b t where t.id=b.id and workid=401)) aselect * from b/* id workid ----------- ----------- 1 301 1 401 2 301 2 201 3 102 3 202 2 401 3 401(8 行受影响) */
--> 测试数据: @b declare @b table (id int,workid int) insert into @b select 1,301 union all select 1,401 union all select 2,301 union all select 2,201 union all select 3,102 union all select 3,202declare @a table (id int) insert into @a select 1 union all select 2 union all select 3INSERT INTO @b(id,workid) SELECT a.id,'401' FROM @a AS a LEFT JOIN @b as b on b.id = a.id AND b.WorkID = '401' WHERE b.id IS NULLSELECT * FROM @b
select t.id,401 as workid from a表 t where not exists(select 1 from b表 where id=t.id and workid=401)
insert b
select id,401 from a
where not exists(select 1 from b where workid = 401 and id = a.id)
declare @b table (id int,workid int)
insert into @b
select 1,301 union all
select 1,401 union all
select 2,301 union all
select 2,201 union all
select 3,102 union all
select 3,202declare @a table (id int)
insert into @a
select 1 union all
select 2 union all
select 3
INSERT INTO @b
SELECT * FROM (SELECT id,'401' as workid FROM @a) A
WHERE not exists(select 1 from @b where id =A.id and workid =401)select * from @b
/*
id workid
----------- -----------
1 301
1 401
2 301
2 201
3 102
3 202
2 401
3 401
*/
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 15:46:48
---------------------------------
--> 生成测试数据表-bif not object_id('b') is null
drop table b
Go
Create table b([id] int,[workid] int)
Insert b
select 1,301 union all
select 1,401 union all
select 2,301 union all
select 2,201 union all
select 3,102 union all
select 3,202
Go
--Select * from b-->SQL查询如下:
insert b
select * from (select distinct id,401 [workid] from b where not exists(select 1 from b t where t.id=b.id and workid=401)) aselect * from b/*
id workid
----------- -----------
1 301
1 401
2 301
2 201
3 102
3 202
2 401
3 401(8 行受影响)
*/
--> 测试数据: @b
declare @b table (id int,workid int)
insert into @b
select 1,301 union all
select 1,401 union all
select 2,301 union all
select 2,201 union all
select 3,102 union all
select 3,202declare @a table (id int)
insert into @a
select 1 union all
select 2 union all
select 3INSERT INTO @b(id,workid)
SELECT a.id,'401' FROM @a AS a LEFT JOIN @b as b
on b.id = a.id
AND b.WorkID = '401'
WHERE b.id IS NULLSELECT * FROM @b