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 4|Noiseworks 5|Wayne Jury 6|Mr Percival 7|Iron Maiden 8|Atmasphere 9|Ian Moss 10|Magnum 11|Strapping Young Lad 12|Slayer 13|Primus 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.