Skip to content
Advertisement

How to iteratively use INSERT INTO in PostgreSQL

I have Table1 with rows I want to iterate over, and insert values from those rows of data into another table.

Table1Name     ->      ID      Name  Date        Type
--------               -------------------------------
a                      101     a     2021-07-01  original
b                      139     b     2021-07-04  original
c                      322     c     2021-07-08  original
d                      431     d     2021-07-09  original

So basically pseudo is:

Loop over Table1:
    INSERT INTO Table2 (id, name, date) VALUES (random(), Table1.name, NOW(), 'original')

Just looping through Table1, adding in one of its values every time.

Advertisement

Answer

I think you just want an INSERT INTO ... SELECT here:

INSERT INTO Table2 (id, name, date)
SELECT RANDOM(), name, NOW()
FROM Table1;

Most SQL operations are inherently set based, meaning if you want to insert all records from one table into another, the operation would be viewed as a single thing.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement