Move Data from One Table to Another Table in One Command

You can move data from one table to another in two SQL commands, an INSERT followed by a DELETE.

But it’s possible to move the data WITH a single command!

Example:

I have a table named MOVE_DATA and another table named MOVE_DATA_v2.

				
					yugabyte=# SELECT * FROM move_data ORDER BY pk;
 pk | data1 |   data2
----+-------+------------
  1 | A     | 2023-09-19
  2 | B     | 2023-08-31
  3 | C     | 2023-04-05
(3 rows)

yugabyte=# SELECT * FROM move_data_v2 ORDER BY pk;
 pk | data1 | data2
----+-------+-------
(0 rows)

The following SQL command will move the data from the MOVE_DATA table to the MOVE_DATA_v2 table:

				
					yugabyte=# WITH move AS (DELETE FROM move_data RETURNING *) INSERT INTO move_data_v2 SELECT * FROM move;
INSERT 0 3

yugabyte=# SELECT * FROM move_data ORDER BY pk;
 pk | data1 | data2
----+-------+-------
(0 rows)

yugabyte=# SELECT * FROM move_data_v2 ORDER BY pk;
 pk | data1 |   data2
----+-------+------------
  1 | A     | 2023-09-19
  2 | B     | 2023-08-31
  3 | C     | 2023-04-05
(3 rows)

Have Fun!