They are obedient guardians of your data.

They don’t weigh very much, and they survive on a simple diet of electrons.

But they are not very interesting unless they are filled with data.

So how do you stuff a database with numbers and dates and other information?

The answer, my friends, is the INSERT command.

Today, we will learn how to insert data into a table one row at a time and multiple rows

at a time.

Insert final line here before we film the video…

Today we will create a database for a social network called “Chitter.”

This lean startup will be an innovative cloud based platform that will disrupt the social

network industry by using big data and machine learning to find a synergy between influencers

and thought leaders.

There will be targeted ads...

So how will we accomplish this?

By letting people impulsively post their thoughts online.

We will differentiate ourselves by adjusting the font size of each post according to the

length of the post.

The longer the post, the smaller the font.

The database behind Chitter will consist of several tables: a “chitter_user” table,

“post” table, and a “follower” table.

The “chitter_user” table will have the following columns: user_id .. username .. encrypted_password

.. email .. and date_joined.

The user_id column will be the auto-generated primary key of this table..

The “post” table will have the following columns: post_id .. user_id .. post_text .. and

posted_on.

The post_id column will also be an auto-generated primary key.

Finally, the “follower” table will identify who follows a particular user.

It will have two columns: user_id, and follower_id.

These 3 tables should be enough for us to begin improving the world through polite,

constructive online discourse.

Before we start, let us see how much data we have in our user table.

A clean slate.

Our start up really is just starting up…

Now to add data.

To begin, let us insert data into the “chitter_user” table.

To do this, use the “INSERT INTO” statement followed by the table name.

Next, list the columns in parentheses for which you have data…

Then the VALUES keyword…

And then the data in parentheses.

The order of the data MUST match the order of the columns.

This is important.

Notice that we used the “DEFAULT” keyword as the user_id.

This is because our database will generate this value for us.

Execute…

To see that the data was successfully added, we will SELECT the user data once more.

Our data is there.

And notice that the user_id was created for us.

Let us add another user.

This time we will not specify values for all fields.

We start with “INSERT INTO” and specify the table.

This time, we will only insert values for the username and encrypted password.

Execute…

And SELECT the rows to confirm success.

Success confirmed…

As before, the user_id was generated for us.

And the email and date_joined fields are NULL because we did not provide values.

“seconduser” better not forget their password...

Can it get any better than this?

Yes, yes it can…

Because with the “INSERT INTO” statement

you can add more than one row in a single query.

The trick is to use commas.

Fortunately, most keyboards come equipped with a comma button.

Let us see how this is done.

This time we will create two posts.

As before, start with the statement “INSERT INTO” followed by the table name: post.

I have configured the “post” table to automatically generate the “post_id” and

the “posted_on” values.

All we need to do is provide the “user_id” and the contents of the post.

The first row will be “Hello World!”.

Add a comma, and then provide the values for the next row.

And execute...

And confirm…

And celebrate...

Surprise.

It is me again, and it is NOT the end of the video.

I’ve negotiated more screen time with the producers.

The INSERT INTO statement enables you to add one or more rows into a table.

Ask yourself this - which is better: A single query inserting 6 rows?

Or 6 queries each inserting a single row?

Or maybe multiple queries adding 1 plus 2 plus 2 plus 1 rows?

Or perhaps 1 plus 2 plus 1 plus 1 … plus 1.

We had better check.

To determine which is faster, we will insert 10000 posts into the “post” table in two

different ways.

For this demonstration, we will use Python with a Postgres database.

Do not panic if you are unfamiliar with Python..

Or Postgres.

Focus on the SQL...

Our objective is to see which is faster: inserting 10000 rows separately,

or in a single big query.

In order to talk to the database, we will use the popular “psyco PG2” module.

To time our work, we need to import the time module.

Let N be the number of rows we add in each batch.

Next, let us construct a list of “N” INSERT queries.

Each query will insert a single sentence into the “post” table.

The other way to do this is with a single, big query that inserts N rows separated by commas.

To tidy up this big query, we need to remove the trailing comma and end it with a semicolon.

Next, load your super secret password…

connect to the database …

and create a cursor…

First, we will insert 10000 rows individually.

Record the starting time.

Execute the query 10000 times.

Commit them to the database.

Record the stop time…

And print a message with the time it took to complete all of the queries.

Now for the BIG query.

Again, record the start time.

Execute the query…

Commit it to the database…

Record the stop time…

And print the results..

And finally, we demonstrate our responsible nature and close both the cursor

and database connection.

Now run…

Inserting the data one row at a time is more than 20 times slower than using one, big INSERT

query with 10,000 values.

This is a “Shining” example of how you

can optimize your queries…

And as a “sanity” check, you can confirm the data is in the “post” table….

It most definitely is...

The INSERT INTO statement makes it a snap to add one or more rows to a table

in a single query.

But exercise caution.

When inserting large volumes of data, they way you word your queries can make a big impact

on how quickly they will run.

The example we saw was very similar to the story of the Tortoise and the Hare, except

it was completely different.

Instead of animals, we worked with SQL queries.

And while slow and steady may win a race in the forest, databases are different places

- all together…

Databases are different places.