How to Import Data into SQL Tables

Following the previous article about creating data tables in SQL, now we want to load data into our freshly created SQL table. In this article, I’ll show you three different import methods:

1. When you want to load the data line by line.
2. When you want to insert the data from a .csv file.
3. When you add rows to your new SQL table that are the results of another SQL query.

Note: This is going to be a practical tutorial, so I encourage you to do the coding part with me.

Note 2: If you are new here, let’s start with these SQL articles first:

1. How to install Python, SQL, R, and Bash (for non-devs)
2. How to install SQL Workbench for PostgreSQL
3. SQL for Data Analysis – Tutorial for Beginners – ep 1
4. How to create a table in SQL

Method #1: Load the Data Line by Line (INSERT INTO)

When we have only a few lines of data, the easiest way is to add them manually. We can do this by using the INSERT SQL statement:

Let’s get back to our test_results table that we created in the previous tutorial.

Currently, it’s an empty table. Let’s change that — and add a line to it using INSERT:

INSERT INTO test_results
VALUES
('Walt', 1, '1980-12-01', 95.50, 'A', TRUE);

Excellent — that inserted a new row into our SQL table.

But let’s see the result and query our table!

SELECT * FROM test_results;

Oh, yeah! Walt’s test results are in the SQL table, indeed!

While this is a very manual process, you can speed it up if you INSERT the rest of the students with one bigger SQL statement:

INSERT INTO test_results
VALUES
('Jesse', 2, '1988-02-11', 74.00, 'C', TRUE),
('Todd', 3, '1987-06-13', 60.00, 'D', TRUE),
('Tuco', 4, '1970-11-11', 15.50, 'F', FALSE),
('Gus', 5, '1975-08-08', 80.00, 'B', TRUE)
;

Query the table once again to check out the results:
SELECT * FROM test_results;

Now, we have 5 students’ data loaded into this sweet SQL table. That was easy as pie, right?

Now I want you to spend a few more seconds reviewing the syntax:

1. INSERT INTO is the SQL keyword.
2. test_results is the name of the table that we want to put the data into.
3. VALUES is another SQL keyword.
4. Then the actual data rows come one by one – each of them between parentheses (()) and separated by commas (,).
5. The field values are also separated by commas (,).
6. Watch out for the data points which data types are TEXT or DATE — these data points have to go between apostrophes (') when you write your SQL query!
7. And never forget the semicolon (;) at the end of your SQL statement!

If you are more of the visual type, here’s your cheat sheet:

Commit your changes!

As we have discussed in the previous article, if you do changes in your database with an SQL manager tool (like pgadmin4 or SQL Workbench), you have to COMMIT them. Always! What does it mean? Learn more here.
But for now, let’s just run this one extra line in your SQL manager:

COMMIT;

Note: If you turned auto-commit on or if you are in the command line and not in an SQL query tool, then you can skip this commit step.

SQL TRUNCATE: empty your table without deleting the table

You have already learned about the DROP TABLE SQL statement that deletes your SQL table. But very often you don’t want to delete your table (because you want to keep its structure), only clear that data. You can do this by using the TRUNCATE TABLE statement.

Type this:
TRUNCATE TABLE test_results;
This will delete all the rows that we have inserted in the table before, but it will keep the table itself.

Don’t forget that you have to commit your changes!

COMMIT;

Note: more about emptying an SQL table here: SQL TRUNCATE TABLE and DROP TABLE tutorial.

Okay, if everything is set, let’s see the…

Method #2: insert a .csv file into an SQL table (COPY)

To be honest, this is a more common scenario than the first method I showed. As a data analyst, you quite regularly get raw data sets in file formats, like .xlsx or .csv or .txt. You can insert these data files using the COPY statement.

The general format of the statement looks like this:

COPY table_name FROM '/path/step/file_name' DELIMITER ' ';

Let me break it down for you:

1. COPY is the SQL keyword that specifies that you’ll insert data from a file into an SQL table.
2. table_name is the name of the table that you want to put the data into. (This is a bit counter-intuitive in the syntax… But we know that SQL is not the most “coder-friendly” tool syntax-wise. So just get over it and simply learn this way.)
3. FROM is another SQL keyword after that you’ll…
4. …specify the name and the location of the file that you want to COPY the data from. This goes between apostrophes.
5. And eventually, you have to specify the field separator in your original file by typing DELIMITER and the field separator itself between apostrophes. So in this case ' ' means that the delimiter would be a space.

Example for COPY (insert .csv data into SQL)

Let’s go through the whole process with an example.

Note: in this example, I’ll help you to create a dummy .csv file. If you have your .csv file, you can just skip STEP #1, #2, and #3.

STEP 1) First, you have to open your Terminal window and connect to your data server.
(Note: At this point assume you know how to do it – if not: this way please.)

STEP 2) Then type this (just copy-paste from here) into the command line:

echo "Walt,1,1980-12-01,95.50,A,TRUE
Jesse,2,1988-02-11,74.00,C,TRUE
Todd,3,1987-06-13,60.00,D,TRUE
Tuco,4,1970-11-11,15.50,F,FALSE
Gus,5,1975-08-08,80.00,B,TRUE" > test_results.csv

This will create a .csv file called test_results.csv.
(In real-life cases you will get this .csv file from someone at your company.)

STEP 3) Double-check your new file: cat test_results.csv.
And find out the exact location of it by typing pwd.

STEP 4) Then you have to log in to PostgreSQL (still in your Terminal window):
psql -U [your_sql_username] -d postgres
(For me it’s psql -U dataguy -d postgres)

STEP 5) Then type the COPY statement we have just discussed above:
\COPY test_results FROM '/home/dataguy/test_results.csv' DELIMITER ',';
And boom, the data is inserted from our freshly created .csv file into our SQL table.

You can even query it from your SQL manager tool to double-check it:

A few comments on the .csv data-load method

• I typed \COPY and not just COPY because my SQL user doesn’t have SUPERUSER privileges, so technically I could not use the COPY command (this is an SQL thing). Typing \COPY instead is the simplest workaround — but the best solution would be to give yourself SUPERUSER privileges then use the original COPY command. (In this video starting at 2:55 I show how to give SUPERUSER privileges to your SQL user. If you are here from one of my online courses, probably we have already fixed this issue in the course.)
• Why we didn’t do the COPY command in our SQL manager tool? Same reason: if you don’t have SUPERUSER privileges, you can’t run the COPY command from an SQL manager tool — only from the command line. If you follow the video that I linked in the previous point, you will be able to run the same COPY statement from pgadmin or SQL Workbench.
• The '/home/dataguy/test_results.csv' is the location and the name of the file, together. Again, we found out the location by using the pwd command.
  
• And finally: if you are uncomfortable with these command-line steps, read the first few articles from my Command Line for Data Analysts article series.

And boom, the data is inserted from a .csv file into our SQL table.
Run this query from your SQL manager:

SELECT * FROM test_results;

Awesome!

Method #3: Insert the output of another SQL query into your SQL table

Do you want to store the output of your SQL query? Not a problem… Maybe you want to save your daily KPIs that are calculated from SQL tables — or you want to have the cleaned version of a data set next to the original. In SQL, you can do that easily.

Say we want to create a table where we want to store only the names from our test_results table. (This is a dummy example but it’ll do the job for now.)

Step 1) Create this new SQL table :

CREATE TABLE student_names
(
name TEXT
);

Step 2)
Use the INSERT INTO statement (that we learned in the “Method #1” section, at the beginning of this article), only instead of typing the values manually, put a SELECT statement at the end of the query.

Something like this:

INSERT INTO student_names
(SELECT name FROM test_results);

Done!
The subquery between parentheses will run first — then its output will be inserted automatically into the recently created student_names table.

Check the result:
SELECT * FROM student_names;

You can even combine this method with SQL functions. For instance, you can calculate the average of the test results and then save that info into a new table. Something like this:

CREATE TABLE test_averages
(
test_average DECIMAL
);

Then:

INSERT INTO test_averages
(SELECT AVG(test_result) FROM test_results);

This new SQL table will store only one value: the average test result… but if we also had math test results, biology test results, and physics test results in other SQL tables, this test_averages table would be the perfect place to collect the different averages.

This was the third – slightly more advanced – way to insert your data into an SQL table. Now go ahead and test these methods on your own data set!

Conclusion

In this article we learned three methods to load data into SQL tables:

1. When you want to INSERT your data manually. (INSERT INTO ___ VALUES (____);)
2. When you want to COPY your data from a file. (COPY ____ FROM '_____' DELIMITER ' ';)
3. When you want to store the output of another SQL query. (INSERT INTO ____ (SELECT ____);)

Now, you know how to create new tables in SQL and how to load data into them!

• If you want to learn more about how to become a data scientist, take my 50-minute video course: How to Become a Data Scientist. (It’s free!)
• Also check out my 6-week online course: The Junior Data Scientist’s First Month video course.

Cheers,
Tomi Mester

Cheers,
Tomi Mester