How to insert into table when subquery returns more value?
Hi I have a Split function that returns strings like below:
declare @a nvarchar(50)= '1,2,3,4,5,6'
select Item from dbo.Split(@a,',')
Result:
Item
--------
1
2
3
4
5
6
Now I want to create a table and insert into two fields from my split function like below:
declare @a nvarchar(50)= '1,2,3,4,5,6'
declare @b nvarchar(50)= '10,20,30,40,50,60'
declare @tblCare table
(
id int ,
count int
)
insert into @tblCare (id,count)
values
(
(select Item from dbo.Split(@a,',')),
(select Item from dbo.Split(@b,','))
)
select * from @tblCare
and i get this
Error: Msg 512, Level 16, State 1, Line 10 Subquery returns more than 1 value. This is not valid when the subquery follows = ,! =, <, <=,>,> = or when a subquery is used as an expression. approval completed.
id count
----------- -----------
(0 row(s) affected)
and its expected output:
id count
---------------
1 10
2 20
3 30
4 40
5 50
6 60
source to share
You can do it like this:
declare @t1 table (ID bigint identity(1, 1), Item nvarchar(max))
declare @t2 table (ID bigint identity(1, 1), Item nvarchar(max))
insert into @t1
select item from dbo.Split(@a,',')
insert into @t2
select item from dbo.Split(@b,',')
insert into @tblCare (id,count)
select T1.Item, T2.Item
from @t1 as T1
inner join @t2 as T2 on T1.ID = T2.ID
First, I create tables with an id column to list the rows of your split data.
And then just concatenate the two results using these rowan trees and pasting them.
source to share
Your function dbo.Split
should return serial no
by which we can join two splits. I am using DelimitedSplit8K from Jeff Moden, which is one of the fastest splitters out there, but you can update your split function to include the serial number using ROW_NUMBER()
.
declare @a nvarchar(50)= '1,2,3,4,5,6'
declare @b nvarchar(50)= '10,20,30,40,50,60'
insert into @tblCare (id,count)
SELECT a.item,b.item
FROM [DelimitedSplit8K](@a,',') a
INNER JOIN [DelimitedSplit8K](@b,',') b
ON a.itemnumber = b.itemnumber
Output
1 10
2 20
3 30
4 40
5 50
6 60
source to share
Don't use a subquery, the insert syntax form is:
insert into table ...
select ...
Where the number and type of select columns match the inserted columns.
I assumed you want to count all calls to separate the returned items:
insert into @tblCare (id, count)
select item, count(*) from
(select item from dbo.Split(@a,',')
union all
select item from dbo.Split(@b,',')) x
group by item
source to share