SQL Data Import, Export, Backup & Restore: LOAD DATA, COPY, mysqldump & Comprehensive Examples

1. What is data import in SQL?

Data import involves loading external data (e.g., CSV, text files) into database tables to populate or update them. Common methods include LOAD DATA (MySQL), COPY (PostgreSQL), and other tools like BULK INSERT (SQL Server) or SQL*Loader (Oracle).

Use Case: Importing customer data from a CSV file into a database table.

Key Considerations:

• File format (e.g., CSV, TSV).
• Data validation and error handling.
• Performance for large datasets.

Support: MySQL (LOAD DATA), PostgreSQL (COPY), SQL Server (BULK INSERT), Oracle (SQL*Loader), SQLite (.import).

2. How do you use LOAD DATA and COPY for data import?

LOAD DATA (MySQL): Imports data from a text file into a table, with options for delimiters, headers, and error handling.

COPY (PostgreSQL): Loads data from a file into a table or vice versa, supporting CSV, text, or binary formats.

3. Can you give an example of LOAD DATA and COPY?

-- Create sample table
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), Salary DECIMAL(10, 2), DepartmentID INT
); -- MySQL: Create a CSV file for import (employees.csv)
-- Content of employees.csv:
-- 1,John,60000.00,1
-- 2,Jane,55000.00,2
-- 3,kristal,65000.00,1 -- MySQL: LOAD DATA
LOAD DATA INFILE '/path/to/employees.csv'
INTO TABLE Employees
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(EmployeeID, FirstName, Salary, DepartmentID); -- PostgreSQL: COPY
COPY Employees (EmployeeID, FirstName, Salary, DepartmentID)
FROM '/path/to/employees.csv'
DELIMITER ','
CSV; -- SQL Server: BULK INSERT
BULK INSERT Employees
FROM 'C:\path\to\employees.csv'
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 1
); -- SQLite: Import
-- In SQLite CLI:
.import employees.csv Employees
-- Note: Requires table creation and .mode csv in SQLite CLI -- Verify data
SELECT * FROM Employees; 

Output (Employees):

EmployeeID | FirstName | Salary | DepartmentID
-----------|-----------|----------|-------------
1 | John | 60000.00 | 1
2 | Jane | 55000.00 | 2
3 | kristal | 65000.00 | 1 

Note:

• MySQL: LOAD DATA INFILE requires file path and appropriate permissions (FILE privilege). Use LOCAL for client-side files.
• PostgreSQL: COPY requires superuser privileges for server-side files; use \copy in psql for client-side.
• SQL Server: BULK INSERT requires ADMINISTER BULK OPERATIONS permission.
• SQLite: Uses .import in the CLI, with .mode csv for CSV files.

4. What is data export in SQL?

Data export involves extracting data from database tables to external files (e.g., CSV, JSON, SQL dumps) for reporting, sharing, or backup purposes. Common methods include SELECT INTO OUTFILE (MySQL), COPY (PostgreSQL), and command-line tools like bcp (SQL Server).

Use Case: Exporting a report of employee salaries to a CSV file.

Key Considerations:

• File format and delimiters.
• Permissions for file access.
• Handling large datasets efficiently.

5. What are common export options?

MySQL:SELECT INTO OUTFILE for files, mysqldump for SQL dumps.

PostgreSQL:COPY to files, pg_dump for backups.

SQL Server:bcp utility, SELECT INTO for new tables, or SSMS export tools.

Oracle:expdp (Data Pump), spool in SQL*Plus.

SQLite:.output and .dump in CLI.

6. Can you give an example of exporting data?

-- MySQL: Export to CSV
SELECT EmployeeID, FirstName, Salary, DepartmentID
INTO OUTFILE '/path/to/employees_export.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM Employees; -- PostgreSQL: Export to CSV
COPY Employees TO '/path/to/employees_export.csv'
DELIMITER ','
CSV HEADER; -- SQL Server: Export using bcp (command-line)
-- Run in command prompt:
-- bcp "SELECT EmployeeID, FirstName, Salary, DepartmentID FROM database_name.dbo.Employees" queryout "C:\path\to\employees_export.csv" -c -T -S server_name -- SQLite: Export to CSV
-- In SQLite CLI:
.output employees_export.csv
.mode csv
SELECT * FROM Employees;
.output stdout -- Verify exported file (example content of employees_export.csv):
-- 1,John,60000.00,1
-- 2,Jane,55000.00,2
-- 3,kristal,65000.00,1 -- Verify data
SELECT * FROM Employees; 

Output (Employees):

EmployeeID | FirstName | Salary | DepartmentID
-----------|-----------|----------|-------------
1 | John | 60000.00 | 1
2 | Jane | 55000.00 | 2
3 | kristal | 65000.00 | 1 

Note:

• MySQL: SELECT INTO OUTFILE requires FILE privilege and a secure file path.
• PostgreSQL: COPY TO requires superuser for server-side files; use \copy in psql.
• SQL Server: bcp is a command-line tool; requires server access and permissions.
• SQLite: Uses .output in CLI for file export.

7. What are backup and restore in SQL?

Backup: Creates a copy of the database (schema and/or data) to protect against data loss, typically as SQL scripts or binary files.

Restore: Reimports the backup to recover the database to a previous state.

Tools:

• MySQL: mysqldump for logical backups, mysql for restore.
• PostgreSQL: pg_dump for backups, pg_restore or psql for restore.
• SQL Server: BACKUP DATABASE for backups, RESTORE DATABASE for restore.
• Oracle: expdp/impdp (Data Pump) or RMAN for backups/restore.
• SQLite: .dump for backups, .read for restore in CLI.

Use Case: Backing up a database before a major update; restoring after a failure.

8. Can you give an example of backup and restore?

-- MySQL: Backup using mysqldump (command-line)
-- Run in terminal:
-- mysqldump -u root -p database_name > backup.sql -- MySQL: Restore
-- Run in terminal:
-- mysql -u root -p database_name < backup.sql -- PostgreSQL: Backup using pg_dump
-- Run in terminal:
-- pg_dump -U postgres database_name > backup.sql -- PostgreSQL: Restore
-- Run in terminal:
-- psql -U postgres -d database_name -f backup.sql -- SQL Server: Backup
BACKUP DATABASE database_name
TO DISK = 'C:\path\to\backup.bak'
WITH FORMAT; -- SQL Server: Restore
RESTORE DATABASE database_name
FROM DISK = 'C:\path\to\backup.bak'
WITH REPLACE; -- SQLite: Backup
-- In SQLite CLI:
.output backup.sql
.dump Employees
.output stdout -- SQLite: Restore
-- In SQLite CLI:
.read backup.sql -- Verify data after restore
SELECT * FROM Employees; 

Output (Employees after restore):

EmployeeID | FirstName | Salary | DepartmentID
-----------|-----------|----------|-------------
1 | John | 60000.00 | 1
2 | Jane | 55000.00 | 2
3 | kristal | 65000.00 | 1 

Note:

• MySQL: mysqldump creates a SQL script; restore with mysql.
• PostgreSQL: pg_dump generates a SQL file; restore with psql.
• SQL Server: BACKUP/RESTORE uses binary .bak files; requires admin privileges.
• SQLite: .dump creates a SQL script; .read restores it.

9. Can you provide a comprehensive example of data import, export, backup, and restore?

-- Create sample table
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), Salary DECIMAL(10, 2), DepartmentID INT
); -- MySQL: Import data from CSV
LOAD DATA INFILE '/path/to/employees.csv'
INTO TABLE Employees
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(EmployeeID, FirstName, Salary, DepartmentID); -- PostgreSQL: Import data
COPY Employees (EmployeeID, FirstName, Salary, DepartmentID)
FROM '/path/to/employees.csv'
DELIMITER ','
CSV; -- Export data to CSV
-- MySQL
SELECT EmployeeID, FirstName, Salary, DepartmentID
INTO OUTFILE '/path/to/employees_export.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM Employees
WHERE Salary > 60000.00; -- PostgreSQL
COPY (SELECT EmployeeID, FirstName, Salary, DepartmentID FROM Employees WHERE Salary > 60000.00)
TO '/path/to/employees_export.csv'
DELIMITER ','
CSV HEADER; -- Backup database
-- MySQL (command-line)
-- mysqldump -u root -p database_name > backup.sql -- PostgreSQL (command-line)
-- pg_dump -U postgres database_name > backup.sql -- SQL Server
BACKUP DATABASE database_name
TO DISK = 'C:\path\to\backup.bak'
WITH FORMAT; -- SQLite (CLI)
.output backup.sql
.dump Employees
.output stdout -- Simulate data loss
DELETE FROM Employees; -- Restore database
-- MySQL (command-line)
-- mysql -u root -p database_name < backup.sql -- PostgreSQL (command-line)
-- psql -U postgres -d database_name -f backup.sql -- SQL Server
RESTORE DATABASE database_name
FROM DISK = 'C:\path\to\backup.bak'
WITH REPLACE; -- SQLite (CLI)
.read backup.sql -- Verify data
SELECT * FROM Employees; 

Output (Employees after restore):

EmployeeID | FirstName | Salary | DepartmentID
-----------|-----------|----------|-------------
1 | John | 60000.00 | 1
2 | Jane | 55000.00 | 2
3 | kristal | 65000.00 | 1 

Output (employees_export.csv):

EmployeeID,FirstName,Salary,DepartmentID
3,kristal,65000.00,1 

Description:

• Import: Loads employees.csv into Employees using LOAD DATA (MySQL) or COPY (PostgreSQL).
• Export: Saves high-salary employees to CSV using SELECT INTO OUTFILE or COPY.
• Backup: Creates a database backup with mysqldump, pg_dump, or BACKUP DATABASE.
• Restore: Recovers data after deletion using the appropriate restore command.
• Integrates prior concepts (SELECT, WHERE, permissions).

10. What are common mistakes in data import/export and backup/restore?

Data Import:

• Incorrect delimiters or file formats, causing data misalignment.
• Ignoring data validation, leading to constraint violations (e.g., duplicate keys).
• Not setting appropriate permissions for file access (e.g., MySQL FILE privilege).

Data Export:

• Exporting sensitive data without encryption or access control.
• Using incorrect file paths, causing permission errors.
• Not including headers in CSV exports, confusing users.

Backup/Restore:

• Not testing backups, leading to corrupted or unusable restores.
• Forgetting to include schema in backups, losing table definitions.
• Not securing backup files, risking data exposure.

General:

• Not handling large datasets efficiently, causing performance issues.
• Ignoring transaction boundaries during import, risking partial loads.
• Not scheduling regular backups, risking data loss.

11. What are best practices for data import/export and backup/restore?

Data Import:

• Validate file format and data before import (e.g., check for duplicates, NULLs).
• Use transactions for imports to ensure consistency (e.g., START TRANSACTION; LOAD DATA; COMMIT;).
• Specify delimiters and line terminators explicitly (e.g., FIELDS TERMINATED BY ',').
• Index tables after large imports for performance.

Data Export:

• Include headers in CSV exports (e.g., CSV HEADER in PostgreSQL).
• Secure output files with permissions or encryption.
• Test exports with small datasets to verify format.

Backup/Restore:

• Schedule regular backups using cron (Linux) or SQL Server Agent.
• Test restores periodically to ensure backup integrity.
• Include schema and data in backups (e.g., mysqldump --databases).
• Store backups in secure, offsite locations.

General:

• Use EXPLAIN to optimize queries used in exports or views for import.
• Monitor import/export performance with RDBMS tools (e.g., PostgreSQL pg_stat_activity).
• Grant minimal file permissions (e.g., MySQL secure_file_priv).
• Log import/export errors for debugging (e.g., MySQL --log-error).
• Compress large backup files to save space (e.g., gzip backup.sql).