CODEWITHSUNDEEP
sql-server
mohammad
mohammad

Reputation: 375

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

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

Upvotes: 3

Views: 1951

Answers (3)

ughai
ughai

Reputation: 9890

Your dbo.Split function should return a serial no on which we can join the two splits. I am using DelimitedSplit8K by Jeff Moden which is one of the fastest splitter there is, but you can update your split function to include a 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

Upvotes: 4

Andrey Korneyev
Andrey Korneyev

Reputation: 26896

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.

Upvotes: 4

Bohemian
Bohemian

Reputation: 425428

Don't use a subquery, the insert syntax form:

insert into table ...
select ...

Where the number and type of the select columns matches the inserted columns.

I have assumed you want a count from all calls to split of the items returned:

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

Upvotes: 0

Related Questions