How to Migrate from BigQuery to SQLServer in PostgreSQL

How do I migrate data and schema from Google BigQuery to Microsoft SQL Server quickly?

Shows step-by-step SQL and tooling workflow to copy data, schema, and constraints from Google BigQuery to Microsoft SQL Server.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What are the core steps to move data?

Export BigQuery tables to Cloud Storage, download as CSV or Parquet, then load files into SQL Server with BULK INSERT, bcp, or Azure Data Factory. Re-create schema, indexes, and constraints before loading.

How do I export BigQuery tables quickly?

Use EXPORT DATA with PARQUET to keep types. Example:
EXPORT DATA OPTIONS(format='PARQUET', uri='gs://ecom_dump/customers_*.parquet') AS SELECT * FROM `shop.Customers`;

Which SQL Server command loads the files?

BULK INSERT and bcp are fastest.Example:
BULK INSERT dbo.Customers FROM 'C:\ecom_dump\customers_000.parquet' WITH (DATA_SOURCE='ParquetSource', FORMAT='PARQUET');

How do I map BigQuery types to SQL Server?

Map STRING→NVARCHAR, INT64→BIGINT, NUMERIC→DECIMAL(38,9), TIMESTAMP→DATETIME2. Always check precision limits.

Type mapping cheat-sheet

STRING <65535 → NVARCHAR(65535);
BOOL → BIT;
ARRAY → JSON or related table.

How to migrate an ecommerce schema end-to-end?

1. Script schema: bq show --schema --format=ddl shop.Customers > customers.sql
2. Adjust DDL to T-SQL, e.g., replace STRING with NVARCHAR(255).
3. Run DDL in SQL Server.
4. Export data as PARQUET.
5. Register an external data source in SQL Server if using PolyBase.
6.Execute BULK INSERT for each table.

How to keep foreign keys intact?

Disable constraints during load:
ALTER TABLE Orders NOCHECK CONSTRAINT ALL;
Load data, then re-enable:
ALTER TABLE Orders WITH CHECK CHECK CONSTRAINT ALL;

Best practices for large tables?

Split export files by 1 GB chunks, compress with gzip, and load in parallel using multiple BULK INSERT sessions.

How to verify row counts?

Compare counts:
SELECT 'Customers' AS table, COUNT(*) FROM dbo.Customers UNION ALL SELECT 'Customers', (SELECT row_count FROM bigqueryinfo WHERE table_name='Customers');

Can I automate in CI/CD?

Yes.Use Azure DevOps or GitHub Actions to run bq export, upload to Azure Blob, then kick off Data Factory pipelines that call stored procedures for BULK INSERT.

Why How to Migrate from BigQuery to SQLServer in PostgreSQL is important

How to Migrate from BigQuery to SQLServer in PostgreSQL Example Usage


-- Full migration for Orders
EXPORT DATA OPTIONS(uri='gs://ecom_dump/orders_*.parquet',format='PARQUET') AS
SELECT * FROM `shop.Orders`;

CREATE TABLE dbo.Orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    order_date DATE,
    total_amount DECIMAL(12,2)
);

BULK INSERT dbo.Orders
FROM 'C:\ecom_dump\orders_000.parquet'
WITH (DATA_SOURCE='ParquetSource', FORMAT='PARQUET');

How to Migrate from BigQuery to SQLServer in PostgreSQL Syntax


-- BigQuery side
EXPORT DATA OPTIONS(
    uri = 'gs://ecom_dump/customers_*.parquet',
    format = 'PARQUET',
    overwrite = true,
    header = false
) AS
SELECT * FROM `shop.Customers`;

-- SQL Server side: create table
CREATE TABLE dbo.Customers (
    id BIGINT PRIMARY KEY,
    name NVARCHAR(255),
    email NVARCHAR(255),
    created_at DATETIME2
);

-- BULK INSERT using PolyBase external data source
BULK INSERT dbo.Customers
FROM 'https://storage.blob.core.windows.net/ecom_dump/customers_000.parquet'
WITH (
    DATA_SOURCE = 'ParquetSource',
    FORMAT = 'PARQUET'
);

-- Repeat for Orders, Products, OrderItems

Common Mistakes

Ignoring data type limits: BigQuery NUMERIC holds 38 digits while SQL Server DECIMAL default may be 18,2. Always declare DECIMAL(38,9) or risk truncation.
Loading data before creating constraints: foreign keys will fail. Disable or postpone constraints until data is fully imported, then re-enable and validate.

Frequently Asked Questions (FAQs)

Can I use PolyBase instead of BULK INSERT?

Yes. Create an external data source pointing to Azure Blob or S3, then use CREATE EXTERNAL TABLE ... WITH (DATA_SOURCE='ParquetSource') and INSERT INTO.