Make an INSERT statement out of result set-CodePudding

I have an SQL script, which returns the data from a Clickhouse table in amount of ~23k rows. I want to insert that exact data into a table in MySQL. Since they're two different DBs and there's no proxy to use (so no INSERT INTO SELECT), seems that the only option i'm left with is to make an insert script with the exact data i have so i can copy-past it and run on another DB.

In another words, let's say i have a script

SELECT date, name, sumIf(events>0) ev
FROM t1
GROUP BY date, name

which returns the data

2023-10-31  'George'    1261169
2023-11-01  'Alan'      1261370
2023-11-02  'Peter'     1261361

How can i modify my script so it returns something like

INSERT INTO t2 VALUES (2023-10-31, 'George', 1261169), (2023-11-01, 'Alan', 1261370), (2023-11-02, 'Peter', 1261361);

CodePudding user response：

here is the code that generates needed insert statement

select concat('INSERT INTO t2 VALUES ', substring(tmp, 2, length(tmp) - 2))
from (
         select replaceAll(toString(groupArray(concat('(', toString(date), ',', name, ',', toString(sum), ')'))), '\'', '') as tmp
         from (
                      (select toDate('2023-10-31') as date, 'George' as name, 1261169 as sum)
                      union all
                      (select toDate('2023-11-01') as date, 'Alan' as name, 1261370 as sum)
                      union all
                      (select toDate('2023-11-02') as date, 'Peter' as name, 1261361 as sum)
                  ) as t
         ) as t2

Output:

INSERT INTO t2 VALUES (2023-10-31,George,1261169),(2023-11-01,Alan,1261370),(2023-11-02,Peter,1261361)

You may use clickhouse playground to test things: https://play.clickhouse.com/play?user=play#c2VsZWN0IDE=

Unfortunately there is not arrayFold function in playground for some reason, this function may simplify code, but I made an ad-hoc solution with string manipulations

CodePudding user response：

There is a more elegant method for this.

SELECT date, name, sumIf(events>0) ev
FROM t1
GROUP BY date, name
FORMAT SQLInsert
SETTINGS 
  output_format_sql_insert_max_batch_size = 3,
  output_format_sql_insert_table_name = 't2'

The output will be something like this:

INSERT INTO t2 VALUES 
  (2023-10-31, 'George', 1261169), 
  (2023-11-01, 'Alan', 1261370), 
  (2023-11-02, 'Peter', 1261361);

Bonus

You can create a link between your ClickHouse and MySQL instance and directly insert data into MySQL using MySQL table engine:

CREATE TABLE t2
(
    ...
)
ENGINE = MySQL('localhost:3306', 'test', 'test', 'bayonet', '123');

INSERT INTO t2
SELECT date, name, sumIf(events>0) ev
FROM t1
GROUP BY date, name