How to Add SQL Data One Row at a Time - dummies

How to Add SQL Data One Row at a Time

By Allen G. Taylor

Most SQL DBMSs support form-based data entry. This feature enables you to create a screen form that has a field for every column in a database table. Field labels on the form enable you to determine easily what data goes into each field. The data-entry operator enters all the data for a single row into the form.

After the DBMS accepts the new row, the system clears the form to accept another row. In this way, you can easily add data to a table one row at a time.

Form-based data entry is easy and less susceptible to data-entry errors than using a list of comma-delimited values. The main problem with form-based data entry is that it is nonstandard; each DBMS has its own method of creating forms. This diversity, however, is not a problem for the data-entry operator.

You can make the form look generally the same from one DBMS to another. (The data-entry operator may not suffer too much, but the application developer must return to the bottom of the learning curve every time he or she changes development tools.) Another possible problem with form-based data entry is that some implementations may not permit a full range of validity checks on the data that you enter.

You can prevent the entry of some bad data by applying constraints to the fields on a data-entry form. This enables you to make sure that the database accepts only data values of the correct type and within a predefined range. Such constraints can’t prevent all possible errors, but they can catch some errors.

If the form-design tool in your DBMS doesn’t let you apply all the validity checks that you need to ensure data integrity, you may want to build your own screen, accept data entries into variables, and check the entries by using application program code. After you’re sure that all the values entered for a table row are valid, you can then add that row by using the SQL INSERT command.

If you enter the data for a single row into a database table, the INSERT command uses the following syntax:

INSERT INTO table_1 [(column_1, column_2, ..., column_n)]
 VALUES (value_1, value_2, ..., value_n) ;

As indicated by the square brackets ([ ]), the listing of column names is optional. The default column list order is the order of the columns in the table. If you put the VALUES in the same order as the columns in the table, these elements go into the correct columns — whether you specify those columns explicitly or not.

If you want to specify the VALUES in some order other than the order of the columns in the table, you must list the column names in the same order as the list of values in the VALUES clause.

To enter a record into the CUSTOMER table, for example, use the following syntax:

INSERT INTO CUSTOMER (CustomerID, FirstName, LastName,
 Street, City, State, Zipcode, Phone)
 VALUES (:vcustid, 'David', 'Taylor', '235 Loco Ave.',
 'El Pollo', 'CA', '92683', '(617) 555-1963') ;

The first VALUE in the third line, vcustid, is a variable that you increment with your program code after you enter each new row of the table. This approach guarantees that you have no duplication of the CustomerID (which is the primary key for this table and must be unique). The rest of the values are data items rather than variables that contain data items.

Of course, you can hold the data for these columns in variables, too, if you want. The INSERT statement works equally well whether you use variables or an explicit copy of the data itself to form the arguments of the VALUES keyword.