Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TableA (
ID INT GENERATED ALWAYS AS IDENTITY,
ColA1 INT, 
ColA2 VARCHAR(30)
);
--
-- INSERT VALUES into existing table
--
INSERT INTO TableA (ColA1, ColA2) VALUES
(10, 'A'),
(20, 'B'), 
(30, 'C');
3 rows affected

--
-- SELECT INTO new table
--
SELECT ID, ColA1+2 AS ColB1, ColA2||'2' AS ColB2 
INTO TableB
FROM TableA;
3 rows affected

--
-- INSERT from SELECT with explicit columns
--
INSERT INTO TableA (ColA1, ColA2) 
SELECT ColB1+1, CONCAT(LEFT(ColB2,1),'3') AS ColB23
FROM TableB;
3 rows affected

SELECT * FROM TableA;


id
cola1
cola2


1
10
A

2
20
B

3
30
C

4
13
A3

5
23
B3

6
33
C3


--
-- INSERT from SELECT without columns
-- Only works when they have the same number of columns. 
--
INSERT INTO TableB
SELECT *
FROM TableA;
6 rows affected

SELECT * FROM TableB;


id
colb1
colb2


1
12
A2

2
22
B2

3
32
C2

1
10
A

2
20
B

3
30
C

4
13
A3

5
23
B3

6
33
C3