SQL Records: Data Entry

	Creating a Record in the SQL

Introduction

To enter data in a table using SQL, you can use the following formula:

INSERT TableName VALUES(Column1, Column2, Column_n)

Alternatively, or to be more precise, you can specify that you are entering data in the table using the INTO keyword between the INSERT keyword and the TableName factor. This is done with the following syntax:

INSERT INTO TableName VALUES(Column1, Column2, Column_n)

The TableName factor must be a valid name of an existing table in the currently selected database. If the name is wrong, the SQL interpreter would simply consider that the table you are referring to doesn't exist. Consequently, you would receive an error.

The VALUES keyword indicates that you are ready to list the values of the columns. The values of the columns must be included in parentheses. Specify the value of each column in the parentheses that follow the VALUES keyword.

Adjacent Data entry

The most common technique of performing data entry requires that you know the sequence of columns of the table in which you want to enter data. With this sequence in mind, enter the value of each field in its correct position.

During data entry on adjacent fields, if you don't have a value for a string field, type two double-quotes to specify an empty field. Imagine you have a table equipped with two string columns. Here is an example that creates a record made of two strings:

INSERT INTO Employees VALUES("Jimmy", "Collen");

Remember that you can pass the SQL statement to the RunSQL() method of the DoCmd object. Here is an example:

Private Sub cmdEnterData_Click()
    DoCmd.RunSQL "INSERT INTO Employees VALUES(""Jimmy"", ""Collen"");"
End Sub

Random Data Entry

The adjacent data entry requires that you know the position of each column. The SQL provides an alternative that allows you to perform data entry using the name of a column instead of its position. This allows you to provide the values of fields in any order of your choice.

To perform data entry at random, you must provide a list of the columns of the table in the order of your choice. You can either use all columns or provide a list of the same columns but in your own order. Here is an example:

CREATE Table Employees(FirstName Text,
                       LastName Text,
                       EmailAddress Varchar,
                       HomePhone Char);

INSERT INTO Employees(FirstName, LastName, EmailAddress, HomePhone)
               VALUES("Gertrude", "Monay", "gmonay@ynb.com", "(104) 972-0416");"

You don't have to provide data for all columns, just those you want, in the order you want. To do this, enter the names of the desired columns on the right side of the name of the table, in parentheses. The syntax used would be:

INSERT TableName(ColumnName1, Columnname2, ColumnName_n)
VALUES(ValueFormColumnName1, ValueFormColumnName2, ValueFormColumnName_n);

Examples

Here is an example that specifies the names of columns:

CREATE Table Employees(FirstName Text,
                       LastName Text,
                       EmailAddress Varchar,
                       HomePhone Char);

INSERT INTO Employees(LastName, EmailAddress, FirstName)
               VALUES("Mukoko", "hmukoko@ynb.com", "Helene");

Here is an example that adds Boolean values on a new record. Here is the table first:

CREATE TABLE Contractors(FullName TEXT,
                         AvailableOnWeekend Bit,
                         OwnsACar Logical,
                         CanShareOwnCar YesNo);

Example 1:

INSERT INTO Contractors VALUES('Arlen Sinoko', 1, 0, 1);

Example 2:

INSERT INTO Contractors VALUES('William Woods', False, False, True);