Search code examples
duplicatescopyclickhouse

Copying large amount of data in ClickHouse except one column

I am trying to duplicate data from one table to another table with slight modification. Essentially taking 1x volume of data and converting into 100x volume of data into another table.

Is there any fastest way to do that same? The schema is identical. I would like to alter one column value while duplicating the data.

Something in this line

INSERT INTO 100x_table (test_id, ......) FROM (SELECT 1, * FROM 10x table EXCEPT test_id)
INSERT INTO 100x_table (test_id, ......) FROM (SELECT 2, * FROM 10x table EXCEPT test_id)
INSERT INTO 100x_table (test_id, ......) FROM (SELECT 3, * FROM 10x table EXCEPT test_id)

and so on

Is there better way of duplicating the table data by changing only one specific column?

Solution

  • You can use the EXCEPT clause e.g.

    SELECT * EXCEPT county FROM uk_price_paid LIMIT 10