CODEWITHSUNDEEP
sqloracle-database
hiyo
hiyo

Reputation: 13

insert into Select Sql

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 ?

Upvotes: 1

Views: 57

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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.

Upvotes: 1

Related Questions