How to insert into a table when subquery return more value?

Question

Hi I crate have a Split function that return rows like bellow :

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 create a table and insert into two field 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 returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

id          count
----------- -----------

(0 row(s) affected)

and its my expect result :

id     count
---------------
1   10  
2   20
3   30
4   40
5   50
6   60

Answer 1

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

Here first I'm creating tables with identity column to enumerate rows of your splitted data.

And then just joining two results using these rownumbers and inserting it.