You haven't told us what error you're getting. But in this case, it's fairly obvious: that's not a valid syntax for the
INSERT
or
SELECT
statements.
STRING_SPLIT
returns a table with multiple rows, one for each value in the split string. SQL has no idea that you want to take the first two values from each and transpose them into columns to insert into your table.
You can't use
STRING_SPLIT
for this.
INSERT INTO Table1 (Id, Field1, Field2, Field3, Field4)
SELECT
@Id,
Left(@First, CharIndex(',', @First) - 1),
Substring(@First, CharIndex(',', @First) + 1, Len(@First)),
Left(@Second, CharIndex(',', @Second) - 1),
Substring(@Second, CharIndex(',', @Second) + 1, Len(@Second))
;
If your parameters might not always contain two values, you will need to add an appropriate fallback.
EDIT: Based on your updated question, you want to join the results of splitting both parameters. That's not quite as simple as it should be, since the
STRING_SPLIT
function doesn't return any sort of row number. But you can fake it with
ROW_NUMBER
:
WITH cteFirst As
(
SELECT
value,
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As RN
FROM
STRING_SPLIT(@First, ',')
),
cteSecond As
(
SELECT
value,
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) As RN
FROM
STRING_SPLIT(@Second, ',')
),
cteData As
(
SELECT
F.value As First,
S.value As Second
FROM
cteFirst As F
INNER JOIN cteSecond As S
ON S.RN = F.RN
)
INSERT INTO Table1 (Id, Field1, First, Second)
SELECT @Id, 0, First, Second
FROM cteData;