<< Home

Chapter 23: Importing and Exporting Data

Importing and exporting are essential for moving data between databases and external systems. SQL engines include commands for bulk loading, copying, and exporting tables in various formats. Efficient data transfer relies on proper file formats, encoding, and matching column definitions.

💡 Always perform imports and exports with explicit column lists and well-defined delimiters. Ambiguous file formats are a frequent source of corruption or silent errors.

LOAD DATA INFILE, COPY, BULK INSERT

Most databases provide fast server-side commands for reading or writing large text files. These bypass normal client insert loops for performance.

LOAD DATA INFILE (MySQL / MariaDB)

LOAD DATA INFILE reads data directly from a file into a table. Files can be local or server-side depending on privileges and configuration.

LOAD DATA INFILE '/var/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, email, created_at);

Each line represents a row; fields correspond to columns. Use IGNORE 1 LINES to skip headers.

⚠️ The server must have permission to read the file, and the secure_file_priv setting may restrict directories. Use LOAD DATA LOCAL INFILE for client-side files (ensure it is enabled).

COPY (PostgreSQL)

PostgreSQL’s COPY reads or writes delimited files in one operation. It supports CSV, text, and binary formats.

-- Import
COPY users (id, name, email, created_at)
FROM '/var/data/users.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',', QUOTE '"');

-- Export
COPY (SELECT * FROM users)
TO '/var/data/users_export.csv'
WITH (FORMAT csv, HEADER true);
💡 When running COPY from client tools, use \copy (psql) to operate on client-local files rather than server-side paths.

BULK INSERT (SQL Server)

SQL Server provides BULK INSERT for high-speed imports. It is similar to PostgreSQL’s COPY.

BULK INSERT dbo.Users
FROM 'C:\data\users.csv'
WITH (
  FIELDTERMINATOR = ',',
  ROWTERMINATOR   = '\n',
  FIRSTROW        = 2
);

Server permissions and file access policies apply. Use OPENROWSET(BULK …) for more flexible data streaming.

⚠️ Always check the target table’s constraints before bulk loading. Disable indexes or triggers temporarily if performance is critical, then rebuild them afterward.

Exporting to CSV or JSON

Export operations let you share query results with other systems, data scientists, or visualization tools. Common targets are CSV and JSON, both supported natively by modern SQL engines.

CSV export

CSV (comma-separated values) is portable and widely supported, though limited in type fidelity. Use text-safe quoting and explicit column names.

-- MySQL / MariaDB
SELECT id, name, email
INTO OUTFILE '/var/data/users_out.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
FROM users;

-- PostgreSQL
COPY (SELECT id, name, email FROM users)
TO '/var/data/users_out.csv'
WITH (FORMAT csv, HEADER true);

-- SQL Server (using BCP utility)
EXEC xp_cmdshell 'bcp "SELECT id, name, email FROM mydb.dbo.Users" queryout "C:\data\users_out.csv" -c -t, -T';
💡 Exporting as UTF-8 ensures compatibility with modern tools. Avoid locale-dependent encodings like Latin-1 unless required.

JSON export

JSON export preserves structure and nested data. Engines that support JSON types can serialize rows directly from queries.

-- PostgreSQL
SELECT json_agg(t) 
FROM (SELECT id, name, email FROM users) AS t;

-- MySQL 8+
SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name, 'email', email))
FROM users;

-- SQL Server
SELECT id, name, email
FROM dbo.Users
FOR JSON AUTO;
⚠️ JSON exports can grow large. Add LIMIT or TOP clauses, or use pagination for manageable file sizes.

External tools and scripts

For large-scale exports, use database utilities or scripting languages with proper drivers. Examples include mysqldump, pg_dump, bcp, or Python scripts using pandas.to_csv() or json.dump(). These tools can stream large results efficiently and preserve schema metadata.

ETL considerations

ETL (Extract, Transform, Load) pipelines handle structured data flows between systems. SQL often serves as the transformation layer. Keep imports and exports idempotent, validated, and traceable.

Extract

Extract from reliable sources. Use stable queries or incremental filters (WHERE updated_at > last_run) to fetch only new or changed rows. Document query versions for reproducibility.

Transform

Perform type conversions, normalizations, and validations before loading. Use staging tables to isolate intermediate results.

-- Example: clean email data in staging
UPDATE staging_users
SET email = LOWER(TRIM(email))
WHERE email IS NOT NULL;
💡 Keep staging and target schemas separate. Apply all data quality checks in the staging phase, and only load valid rows into production tables.

Load

Load transformed data efficiently. Use bulk commands (COPY, LOAD DATA INFILE, BULK INSERT) where possible. Wrap in transactions for atomicity and to allow rollback on failure.

Validation and audit

After each load, verify counts and constraints. Record timestamps and file hashes for audit trails.

-- Example validation
SELECT COUNT(*) AS src_count FROM staging_users;
SELECT COUNT(*) AS tgt_count FROM users;

Automation and scheduling

Use external schedulers or orchestration tools (cron, Airflow, etc.) to automate ETL. SQL scripts should log start and end times, row counts, and errors for monitoring.

⚠️ Never overwrite production data without backup. Test imports on isolated copies and validate checksums before committing.