Insert array into separate rows in sql

App Building
1

I have a multi select box that returns a possible array. How would i enter each possible value in a new row in the second table?

DECLARE @Output As TABLE (leadId INT)

INSERT INTO [lead] (FirstName, LastName, Company, Address1, Address2, City, StateId, ZipCode, PhoneNumber1, PhoneNumber2, Email, LeadNotes, CreatedDate, LeadSource, Qualified, WorkType, Category)
OUTPUT INSERTED.leadId INTO @Output
VALUES ({{textInputFirstName.value}}, {{textInputLastName.value}}, {{textInputCompany.value}}, {{textInputAddress1.value}}, {{textInputAddress2.value}}, {{textInputCity.value}}, {{selectState.value}}, {{textInputZipCode.value}}, {{textInputPhone1.value}}, {{textInputPhone2.value}}, {{textInputEmail.value}}, {{textInputLeadNotes.value}}, {{moment(dateTimeCreatedDate.value)}}, {{selectLeadSource.value}}, {{selectQualified.value}}, {{selectWorkType.value}}, {{selectCategory.value}} )

DECLARE @leadId INT
SELECT @leadId = leadId FROM @OUTPUT

INSERT INTO
leadTrades(
LeadId,
TradeTypeId)
VALUES
(
@leadId,
{{multiselectTrades.value}})

2

Hey @nroeder!

Sorry about the late reply, is it correct that you're using MSSQL here? This looks like something you might have to disable prepared statements for. With that, you can use a mapper to generate the usual syntax for inserting multiple rows into a database.

With prepared statements disabled, I imagine something like the following can work:

VALUES
{{multiselectTrades.value.map(typeId => `(@leadId, '${typeId}')`).join(",")}}

Note: Once you've disabled prepared statements you'll also have to manually put '' around the other values you're passing to your table, e.g. '{{textInputFirstName.value}}' instead of {{textInputFirstName.value}}

Can you let us know if that works?