How do you import and export data using SQL?

Importing and exporting data in SQL involves using database-specific tools and commands to move data between databases and external files like CSVs. Most SQL databases provide built-in utilities for these tasks, though the exact syntax and methods vary by system. For example, MySQL uses LOAD DATA INFILE and SELECT INTO OUTFILE, while PostgreSQL uses COPY commands. These operations typically require proper file permissions and an understanding of data formats to ensure accuracy.

To export data, you can use SQL commands to write query results to a file. In MySQL, SELECT * FROM table INTO OUTFILE '/path/file.csv' FIELDS TERMINATED BY ',' exports a table to a CSV. PostgreSQL’s COPY (SELECT * FROM table) TO '/path/file.csv' WITH CSV HEADER achieves a similar result. SQL Server offers bcp (Bulk Copy Program) for command-line exports or the Import/Export Wizard in SQL Server Management Studio (SSMS). These tools let you specify delimiters, encodings, and error handling. For instance, bcp Database.Schema.Table OUT "file.csv" -c -t, -S server -U user exports a table with comma-separated values. Always verify file paths and permissions, as databases often restrict file system access for security.

For importing data, commands like MySQL’s LOAD DATA INFILE '/path/file.csv' INTO TABLE table FIELDS TERMINATED BY ',' map CSV columns to table fields. PostgreSQL’s COPY table FROM '/path/file.csv' WITH CSV HEADER works similarly. SQL Server uses BULK INSERT table FROM '/path/file.csv' WITH (FIELDTERMINATOR = ',') or SSMS’s import wizard. Ensure the target table’s schema matches the data file’s structure, including data types and column order. For example, if a CSV has a date column, the table must have a compatible type (e.g., DATE or DATETIME). Handling errors during import—like mismatched data types or missing columns—is critical; some tools allow skipping errors or logging them for review. For large datasets, batch imports or disabling indexes temporarily can improve performance.

Tools and considerations vary by database. GUI tools like MySQL Workbench, pgAdmin, or SSMS simplify imports/exports through visual workflows. Command-line tools (e.g., psql for PostgreSQL or sqlcmd for SQL Server) are preferred for automation. Data formats like JSON or XML may require additional parsing steps. Always validate data post-transfer: run SELECT COUNT(*) FROM table to confirm row counts or spot-check records. Security practices include avoiding plaintext credentials in scripts and restricting file system access. For example, MySQL’s secure_file_priv setting limits where files can be read/written. When moving data across systems, consider encoding mismatches (e.g., UTF-8 vs. Latin-1) and use conversion tools if needed.