问个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
