SQLite - Insert Data

To insert data into an SQLite database, use the INSERT statement.

When you use this statement, you specify which columns to insert data into, as well as the data that will be inserted. The INSERT statement adds a new row to the table with the specified data.

Let's add a row of data to our Artists table.

Run the following statement:

Verify that the data was Inserted

You can run a quick SELECT statement to check that the data was inserted.

More about the SELECT statement later, but for now, run the following statement:

This should result in the following being displayed:

sqlite> SELECT * FROM Artists;
1|Joe Satriani

A Shortcut

There's another way to write the SELECT statement that doesn't use so much code.

You can simply omit the column names from the statement, and the fields will still be populated.

However, this method requires that you provide a value for all columns.

Like this:

Now, running the same SELECT statement will return the following:

sqlite> SELECT * FROM Artists;
1|Joe Satriani
2|Steve Vai

About the Primary Key Field

By now you may have noticed that the ArtistId field is getting its value automatically. In our first INSERT, we didn't supply a value for that column. In our second INSERT we provided NULL as the value.

This is happening because the column is of type INTEGER PRIMARY KEY. When a column has been assigned INTEGER PRIMARY KEY, this is an alias for ROWID. With a ROWID, if a value is not provided by the INSERT statement, SQLite automatically inserts its own integer into that field.

The value of the ROWID integer is usually one higher than the largest ROWID currently in use. On an empty table, this value will start at 1.

So the end result is, if you don't provide a value for that field, SQLite will.

Adding Multiple Rows

You can use as many INSERT statements as you need to add more data.

And now our SELECT statement will return the following:

sqlite> SELECT * FROM Artists;
1|Joe Satriani
2|Steve Vai
3|The Tea Party
5|Wayne Jury
6|Mr Percival
7|Iron Maiden
9|Ian Moss
11|Strapping Young Lad
14|Pat Metheny
15|Frank Gambale
16|Frank Zappa
17|The Wiggles

Next we'll have a closer look at selecting data from our database.