当前位置: 代码迷 >> Sql Server >> 问个insert select复合语句解决思路
  详细解决方案

问个insert select复合语句解决思路

热度:39   发布时间:2016-04-27 16:19:34.0
问个insert select复合语句
我不太懂mssql的语法,请帮帮忙。

insert   into   rec(opid,productid)   values((select   id   from   operator   where   name= 'sjm '),(select   id   from   product   where   name= 'pen '));

错误提示:
Subqueries   are   not   allowed   in   this   context.   Only   scalar   expressions   are   allowed

要怎么做呢?

------解决方案--------------------
insert into rec(opid,productid)
select * from (select id from operator where name= 'sjm ')a,(select id from product where name= 'pen ')b

------解决方案--------------------
select id,id1=identity(int,1,1) into #t1 from operator where name= 'sjm '
select id,id1=identity(int,1,1) into #t2 from product where name= 'pen '

if (select count(*) from #t1)> (select count(*) from #t2)
begin
insert into rec(opid,productid)
select #t1.id,#t2.id
from #t1
left join #t2 on #t1.id1=#t2.id1
end
else
begin
insert into rec(opid,productid)
select #t1.id,#t2.id
from #t2
left join #t1 on #t1.id1=#t2.id1
end

drop table #t1,#t2
------解决方案--------------------
declare @a int,@b int
set @a = select id from operator where name= 'sjm '
set @b = select id from product where name= 'pen '

insert into rec(opid,productid)
values(@a,@b)
或者

insert into rec(opid,productid)
select (select id from operator where name= 'sjm '),
(select id from product where name= 'pen ')


------解决方案--------------------
declare @operator table(id int,name varchar(10))
declare @product table(id int,name varchar(10))
declare @rec table(opid int,productid int)

insert @operator select 1, 'aaa '
union all select 2, 'sjm '
insert @product select 11, 'bbb '
union all select 22, 'pen '
select * from @operator
select * from @product
insert into @rec(opid,productid)
select (select id from @operator where name= 'sjm '),
(select id from @product where name= 'pen ')
select * from @rec
  相关解决方案