CHum 240

SQL INSERT Statement

The SQL INSERT statement adds a new row to a table. You may
get the idea from the name INSERT that you
can pick where the row will be placed in the table, but you
can't. The new row is just added somewhere in the table.
We'll talk about ordering table contents later.

To add a movie to our new table we would use the following

  insert into movie (title, length, score) 
  values ('z', 126, 2);

The order of the columns listed after the table name has
to match the order of the values listed in the values
clause. When writing programs, I try to avoid making errors
in the order of column names and values by formating the
statement as follows:

  insert into
   movie (score, title,  length)
  values (8,     '2001', 160);

Remember, the SQL language doesn't care about spaces or
line breaks, so this lets you make sure that the columns and
values line up properly. Of course, if the table has too
many columns, or if a value is very long, formatting the
statement this way won't help very much; you will just have
to be cautious.  

A shorter version of the INSERT statement allows you to 
leave out the list of columns after the table name, but 
using this form can lead to problems if you forget the order
of the columns, or if someone adds a new column to the 

  insert into movie values ('high noon', 85, '7');  

You are only being shown this form so that when you see it
you will recognize it and the dangers associated with it.
Doing the insert this way is dangerous! Consider
what would happen if you or someone else had added a column
to the table

Inserting large quantities of data into a database usually
requires you to create a text file with insert statements
for all the data you want to add. This can be done by hand
or by writing a program that reads the data in the original
form and write the data as INSERT statements.

The DBMS client may allow data to be imported in other 
ways. MySQL accepts CSV files, which is a format used by
most spreadsheet programs. SQLite can import data where rows
are delimited by lines and columns are separated by the 
vertical bar ('|') character. (You can also specify an other
separator--use ".help".)

Often you can find a program that provides a graphical
user interface (GUI) to the DBMS, which may make data entry

- - - -

Copyright ©2007 Brigham Young University