Import and Export Data in SQL – Moving Data In and Out of Databases

Overview

Importing and exporting data are essential tasks for database administrators and developers. These operations allow you to move data between databases, migrate across systems, or load data from files like CSV, Excel, or SQL scripts into your database.

Export: Saving data from a database to an external file
Import: Loading external data into a database table



Common Use Cases

  • Migrating data between environments (e.g., development to production)
  • Loading data from spreadsheets or CSV files
  • Backing up data in portable formats
  • Sharing data with external systems or applications



Data Export Methods

1. Exporting Data in MySQL

Using mysqldump:

mysqldump -u root -p my_database > my_database_export.sql

Export Table to CSV:

SELECT * FROM employees
INTO OUTFILE '/var/lib/mysql-files/employees.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';


2. Exporting Data in PostgreSQL

Using pg_dump:

pg_dump -U postgres -F c -f my_database.backup my_database

Export Table to CSV:

COPY employees TO '/tmp/employees.csv' DELIMITER ',' CSV HEADER;


3. Exporting in SQL Server

bcp "SELECT * FROM Employees" queryout "C:\exports\employees.csv" -c -t, -S SERVERNAME -U sa -P password

Or use SQL Server Management Studio (SSMS):
Right-click the database > Tasks > Export Data




Data Import Methods

1. Importing CSV into MySQL

LOAD DATA INFILE '/var/lib/mysql-files/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;


2. Importing CSV into PostgreSQL

COPY employees FROM '/tmp/employees.csv' DELIMITER ',' CSV HEADER;


3. Importing in SQL Server

Use the Import Wizard in SSMS:
Right-click the database > Tasks > Import Data

You can also use T-SQL with BULK INSERT:

BULK INSERT employees
FROM 'C:\imports\employees.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2);



Supported Formats

  • CSV – Common and easy to work with
  • SQL – Ideal for full data and structure export
  • JSON / XML – Used in APIs and web applications
  • Excel – Often used in business environments



Best Practices

  • Always validate the data before importing
  • Use transactions to safely import large datasets
  • Ensure proper data types and formats (e.g., date, numeric)
  • Backup the database before importing
  • Watch for duplicate entries and primary key violations
  • Automate using scripts or ETL tools (like Talend, Apache NiFi, or SQL Server Integration Services)


Conclusion

Mastering data import and export techniques is crucial for effective database management. Whether you're migrating data between systems, loading information from external sources, or creating backups, understanding these methods will make your database workflows more efficient and reliable.