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:

INSERT INTO Artists (ArtistName)

VALUES ('Joe Satriani');

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:

SELECT * FROM Artists;

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:

INSERT INTO Artists VALUES (NULL, 'Steve Vai');

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

sqlite> SELECT * FROM Artists;1|Joe Satriani2|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.

INSERT INTO Artists VALUES (NULL, 'The Tea Party');

INSERT INTO Artists VALUES (NULL, 'Noiseworks');

INSERT INTO Artists VALUES (NULL, 'Wayne Jury');

INSERT INTO Artists VALUES (NULL, 'Mr Percival');

INSERT INTO Artists VALUES (NULL, 'Iron Maiden');

INSERT INTO Artists VALUES (NULL, 'Atmasphere');

INSERT INTO Artists VALUES (NULL, 'Ian Moss');

INSERT INTO Artists VALUES (NULL, 'Magnum');

INSERT INTO Artists VALUES (NULL, 'Strapping Young Lad');

INSERT INTO Artists VALUES (NULL, 'Slayer');

INSERT INTO Artists VALUES (NULL, 'Primus');

INSERT INTO Artists VALUES (NULL, 'Pat Metheny');

INSERT INTO Artists VALUES (NULL, 'Frank Gambale');

INSERT INTO Artists VALUES (NULL, 'Frank Zappa');

INSERT INTO Artists VALUES (NULL, 'The Wiggles');

And now our SELECT statement will return the following:

sqlite> SELECT * FROM Artists;1|Joe Satriani2|Steve Vai3|The Tea Party4|Noiseworks5|Wayne Jury6|Mr Percival7|Iron Maiden8|Atmasphere9|Ian Moss10|Magnum11|Strapping Young Lad12|Slayer13|Primus14|Pat Metheny15|Frank Gambale16|Frank Zappa17|The Wiggles

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