insert into Select Sql

Question

Please answer me if you know how to insert values into columns added into an existing table. Your answer will be highly appreciated

insert into table a
(name, id)
select col
from table2
;

select col from table2; 

this sql col data is 'john','1' this is impassible ?

Answer 1

You may try using the base string functions here:

INSERT INTO TABLE a (name, id)
SELECT
    SUBSTR(col, 1, INSTR(col, ',') - 1),
    SUBSTR(col, INSTR(col, ',') + 1)
FROM table2;

If you don't want to insert the insert quotes, then we can slightly modify the above to the following:

INSERT INTO TABLE a (name, id)
SELECT
    SUBSTR(col, 2, INSTR(col, ',') - 2),
    SUBSTR(col, INSTR(col, ',') + 2, LEN(col) - INSTR(col, ',') - 2)
FROM table2;

As has been mentioned in the above comments, storing CSV data in your tables is generally not a good idea. But your question is a good one, because you are trying to solve that problem by moving each value to a new column.