Import / Export in SQL – Data Transfer Explained
What Is Import and Export?
In SQL, Export means taking data out of a database and saving it to a file, such as CSV, Excel, or SQL dump. Import means loading data from an external file into a database.
These operations are commonly used when:
- Moving data between environments (e.g., development to production)
- Backing up or restoring data
- Sharing data with other systems or tools (e.g., Excel)
- Migrating from one database to another
Exporting Data from SQL
Example: Export all customer data to a CSV file.
MySQL
SELECT * FROM customers
INTO OUTFILE '/var/lib/mysql-files/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';PostgreSQL
COPY customers TO '/tmp/customers.csv' DELIMITER ',' CSV HEADER;You can also export data using GUI tools like MySQL Workbench or pgAdmin.
Importing Data into SQL
Example: Load data from a CSV file into the customers table.
MySQL
LOAD DATA INFILE '/var/lib/mysql-files/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';PostgreSQL
COPY customers FROM '/tmp/customers.csv' DELIMITER ',' CSV HEADER;GUI-based import tools allow you to map file columns to table columns during import.
Common File Formats
.csv– Comma-separated values.sql– SQL dump (data and/or schema).xlsx– Excel file (usually imported/exported via external tools).json– Common for APIs and NoSQL integration
Real-World Use Cases
- Exporting customer data for reporting
- Importing a new dataset from a partner or client
- Migrating databases between systems
- Setting up automated daily backups
Summary Table
| Operation | Description | Methods |
|---|---|---|
| Export | Save data from SQL to a file | SELECT INTO OUTFILE, COPY TO, GUI tools |
| Import | Load data from a file into SQL | LOAD DATA INFILE, COPY FROM, GUI tools |
Conclusion
Use export operations to share, move, or back up data from your SQL databases. Use import operations to bring in external data. Whether you’re working with CSV, SQL dump files, or other formats, these are essential operations in database administration and integration.