CODEWITHSUNDEEP
mysqlsql
Roxy
Roxy

Reputation: 3

How to add more values into a declared variable?

I wanted to insert a values into a declared variable. This is how it is so far :

INSERT INTO DP_Transaction(Trans_Reference, Trans_Action, Trans_Name,
Trans_Date, Trans_Comment) 
VALUES(@formId, 'Cancelled by', 'Workflow Manager', '2021-03-29 09:52:28.257', 'test')

And the values I want to add inside @formid is something like below:

'12323985',
'39864345',
'89426596',
'97070302',
'56746838'

And my expected result is all the values inside the statement will be insert into these @formId rows: '12323985', '39864345', '89426596', '97070302', '56746838'

Can anyone help?

Upvotes: 0

Views: 46

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

You could place the form ID values into a subquery and then rephrase the insert as an INSERT INTO ... SELECT:

INSERT INTO DP_Transaction (Trans_Reference, Trans_Action,
    Trans_Name, Trans_Date, Trans_Comment)
SELECT
    formid,
    'Cancelled by',
    'Workflow Manager',
    '2021-03-29 09:52:28.257',
    'test'
FROM
(
    SELECT '12323985' AS formid UNION ALL
    SELECT '39864345' UNION ALL
    SELECT '89426596' UNION ALL
    SELECT '97070302' UNION ALL
    SELECT '56746838'
) t;

If you were using a different database, e.g. SQL Server, then you could have used APPLY along with a string splitting function. But this is not an option in MySQL to my knowledge.

Upvotes: 1

Related Questions