Insert SQL array

Question

I'm trying to write an insert statement which would get data from an array.

I have two variables, one is BusinessID and one is BusinessType. A business can have multiple types. For example BusinessType = (1,2,3,4,5), however BusinessID would only ever be a single number e.g. BusinessID = 1

Is there a function in mssql to be able to do something like;

for BusinessType in BusinessType
   insert into Business 
   Values(BusinessID,BusinessType)

This would ideally give me the result

BusinessID BusinessType
     1          1
     1          2
     1          3
     1          4
     1          5

Normally it's the responsibility of the application layer to translate that comma-separated list into a series of INSERT statements. — tadman
– tadman, Commented Oct 17, 2017 at 17:50
BusinessType is an int currently, but it can be something else if needed. — jol1234
– jol1234, Commented Oct 17, 2017 at 17:53
You could look into user defined table types and pass in a table valued parameter, or you can split the string (which is not good for performance), or you can generate multiple insert statements (also not good for performance). — Sean Lange
– Sean Lange, Commented Oct 17, 2017 at 17:54
A table called Business should contain records where each record represents a "business" (whatever this is in your context). If that table Business has a column called BusinessID, then this should be the ID to identify one record (or one "business"). It doesn't seem to make sense to have multiple records in Business that have the same BusinessID. It seems you are trying to build a kind of bridge table combining "businesses" with "business types". You should think over your database design or at least your naming pattern. — Thorsten Kettner
– Thorsten Kettner, Commented Oct 17, 2017 at 18:02

John Cappelletti · Accepted Answer · 2017-10-17 18:05:03Z

1

If not 2016, here is an inline approach to split your string

Uncomment the Insert line if satisfied with the results.

Example

Declare @BusinessID int = 1
Declare @Type varchar(max) = '1,2,3,4,5'  -- notice no ()'s

--Insert Into Business (BusinessID,BusinessType) 
Select BusinessID = @BusinessID
      ,BusinessType = B.i.value('(./text())[1]', 'int')
 From  (Select x = Cast('<x>' + replace(@Type,',','</x><x>')+'</x>' as xml).query('.')) as A 
 Cross Apply x.nodes('x') AS B(i)

Returns

BusinessID  BusinessType
1           1
1           2
1           3
1           4
1           5

edited Oct 17, 2017 at 18:05

answered Oct 17, 2017 at 17:58

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert SQL array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related