How to Migrate from SQL Server to ClickHouse in PostgreSQL

How do I migrate a SQL Server database to ClickHouse quickly?

Move schemas and data from SQL Server to ClickHouse using CSV, ODBC, or INSERT SELECT for high-speed analytics.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why choose ClickHouse over SQL Server?

ClickHouse delivers sub-second analytics on billions of rows. Columnar storage, vectorized execution, and compression lower storage cost and speed up aggregation queries compared with SQL Server.

What are the key migration steps?

Export schema ➜ map data types ➜ create tables in ClickHouse ➜ transfer data ➜ verify ➜ optimize.

How do I export SQL Server tables?

Run bcp or sqlcmd to dump each table to CSV.Example:

bcp "SELECT * FROM dbo.Customers" queryout customers.csv -c -t"," -S sql-prod -U sa -P P@ssw0rd

How do I create equivalent ClickHouse tables?

Replace SQL Server types with ClickHouse types and add an ORDER BY key:

CREATE TABLE Customers ( id UInt32, name String, email String, created_at DateTime ) ENGINE = MergeTree ORDER BY id;

Orders table example

CREATE TABLE Orders ( id UInt32, customer_id UInt32, order_date Date, total_amount Decimal(12,2) ) ENGINE = MergeTree ORDER BY (customer_id, order_date);

How can I bulk-load CSV dumps?

Use clickhouse-client --query with INSERT FORMAT CSV:

clickhouse-client --query "INSERT INTO Customers FORMAT CSV" < customers.csv

Can I migrate without files?

Yes.Use the odbc table function to stream directly:

INSERT INTO Customers SELECT * FROM odbc('Driver=ODBC Driver 18 for SQL Server;Server=sql-prod;Uid=sa;Pwd=P@ssw0rd;', 'dbo', 'Customers');

How do I validate row counts?

Run SELECT count() on each side and compare.

Which optimizations matter after load?

Run OPTIMIZE TABLE ...FINAL to merge parts, set proper primary_key and partition_by, and add materialized views for rollups.

What about incremental sync?

Schedule SQL Server Change Tracking exports or use an ETL tool like Debezium ➜ Kafka ➜ ClickHouse’s KafkaEngine.

Why How to Migrate from SQL Server to ClickHouse in PostgreSQL is important

How to Migrate from SQL Server to ClickHouse in PostgreSQL Example Usage


-- Migrate OrderItems directly without CSV
INSERT INTO OrderItems
SELECT id, order_id, product_id, quantity
FROM odbc('Driver=ODBC Driver 18 for SQL Server;Server=sql-prod;Uid=sa;Pwd=P@ssw0rd;',
          'dbo', 'OrderItems');

How to Migrate from SQL Server to ClickHouse in PostgreSQL Syntax


-- General pattern 1: CSV import
bcp "SELECT * FROM dbo.&lt;table&gt;" queryout &lt;table&gt;.csv -c -t"," -S &lt;server&gt; -U &lt;user&gt; -P &lt;pwd&gt;
clickhouse-client --query "INSERT INTO &lt;table&gt; FORMAT CSV" < &lt;table&gt;.csv

-- General pattern 2: Direct ODBC pull
INSERT INTO &lt;ch_table&gt;
SELECT col_list
FROM odbc('<connection_string>', '<schema>', '<table>');

-- Example: migrate Orders
CREATE TABLE Orders (
  id UInt32,
  customer_id UInt32,
  order_date Date,
  total_amount Decimal(12,2)
) ENGINE = MergeTree
ORDER BY (customer_id, order_date);

bcp "SELECT id, customer_id, CAST(order_date AS DATE), total_amount FROM dbo.Orders" queryout orders.csv -c -t"," -S sql-prod -U sa -P P@ssw0rd
clickhouse-client --query "INSERT INTO Orders FORMAT CSV" < orders.csv

Common Mistakes

Skipping ORDER BY keys. Without an ORDER BY clause on MergeTree engines, queries become painfully slow. Always choose a key that matches your most common filters, e.g., (customer_id, order_date).
Using NVARCHAR without length limits. Mapping all text to ClickHouse String inflates storage. Prefer Nullable(FixedString(N)) for short codes or LowCardinality(String) for repeated values.