In this lesson, you will learn how to move data in and out of your database using standard exchange formats to archive information or migrate it between systems.
Why Use Standard Formats?
While we spend most of our time using INSERT or SELECT statements, sometimes we need to handle data in bulk. Instead of writing thousands of individual statements, we use "exchange formats"—the most common being CSV (Comma-Separated Values).
CSV files are universal; they act as a "lingua franca" that almost every spreadsheet program, database system, and data analysis tool can read. By exporting data to a CSV, you create a portable archive of your table's state. By importing from one, you can populate a database table with thousands of rows in a single operation.
The Import/Export Workflow
When you export data, you are essentially taking the result of a SELECT statement and saving it as a flat file. When you import data, you are taking that file and mapping its columns to your existing table schema.
Imagine you have a Customers table. To export your current data, you might use a command-line tool or a database interface to generate a file that looks like this:
id,name,email
1,Alice Smith,alice@example.com
2,Bob Jones,bob@example.com
To bring this data into a new database, you would ensure your table schema (created using the CREATE TABLE concepts we covered earlier) matches these columns. You then use your database’s import utility to map the file content into the table:
-- Conceptual import logic
COPY Customers FROM '/path/to/data.csv' WITH (FORMAT csv, HEADER true);
Critical Considerations
When moving data, remember that your Constraints and Data Quality rules are still in effect. If your email column has a UNIQUE constraint or a NOT NULL constraint, the import process will fail if the CSV file contains duplicates or missing values. Importing is not just moving text; it is an operation that must respect the integrity of your schema.
Key Takeaway: Using standard exchange formats like CSV allows you to reliably migrate or archive data while ensuring that your established schema constraints continue to maintain data integrity.