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

hi crate have split function return rows bellow :

declare @a nvarchar(50)= '1,2,3,4,5,6' select item dbo.split(@a,',') 

result :

item -------- 1 2 3 4 5 6 

now want create table , insert 2 field split function 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 @tblcare (id,count)  values  (    (select item dbo.split(@a,',')),    (select item dbo.split(@b,',')) )  select * @tblcare 

and

error : msg 512, level 16, state 1, line 10 subquery returned more 1 value. not permitted when subquery follows =, !=, <, <= , >, >= or when subquery used expression. statement has been terminated.

id          count ----------- -----------  (0 row(s) affected) 

and expect result :

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

you can this:

declare @t1 table (id bigint identity(1, 1), item nvarchar(max)) declare @t2 table (id bigint identity(1, 1), item nvarchar(max))  insert @t1 select item dbo.split(@a,',')  insert @t2 select item dbo.split(@b,',')        insert @tblcare (id,count) select t1.item, t2.item @t1 t1      inner join @t2 t2 on t1.id = t2.id 

here first i'm creating tables identity column enumerate rows of splitted data.

and joining 2 results using these rownumbers , inserting it.