Data Entry Using the Enterprise Manager or Server
After displaying the table in the SQL Server Enterprise
Manager or the Server Explorer, to enter new data, click an empty cell and type
the necessary value. After finishing with one cell, you can press Enter, Tab or
click another cell. You can start this operation on the most left cell and
continue with the cells on its right. When you finish with a row of cells and
move to another row, the interpreter creates (or updates) a record. Therefore,
entering data also self-creates a record. This also means that, when using the
table in the SQL Server Enterprise Manager or the Server Explorer, you will not
have to formally create a record of a table: it is automatically created when
you enter data.
While performing data entry, the user may skip some fields
if the information is not available. The user can skip only columns that allow
NULL values. If a column was configured as NOT accepting NULL
values, the user must enter something in the field, otherwise he would receive
an error and the table would not allow going further.
Data Entry Using the SQL Query Analyzer
In the SQL, data entry is performed using the INSERT
combined with the VALUES keywords. The primary statement uses the
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 database you are using. If the name is wrong, the
SQL 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. 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 subsequent list in mind, enter the value of
each field in its correct order in the parentheses of the above formula.
||In the previous paragraphs, we were stating
"you" as if you will be the one performing data entry. In
reality, the user will be performing data entry on your products.
Therefore, it is your responsibility to reduce, as much as possible, the
likelihood of mistakes. Of course, there are various ways, through a
"visual" application such as Microsoft Visual C++, etc, that you can take care of this.
If the column is a BIT data type, you must
specify one of its values as 0 or 1.
If the column is a numeric type, you should pay
attention to the number you type. If the column was configured to receive
an integer (int, bigint, smallint), you should
provide a valid natural number without the decimal separator.
If the column is for a decimal number (float, real,
decimal, numeric), you can type the value with its character
separator (the period for US English).
If the column was created for a date data type, make
sure you provide a valid date.
If the data type of a column is a string type, you
should include its entry between single quotes. For example, a shelf
number can be specified as 'HHR-604' and a middle initial can be given as
Here is an example
INSERT Countries VALUES('Sweden',449964,8875053,'Stockholm','se')
The list of values doesn't have to be typed on the same
line. You can use one for each value. Here is example:
In the same way, the parentheses can be written on their own
INSERT INTO Country VALUES
'Mongolia', 1565000, 2694432, 'Ulaanbaator','mn'
The adjacent data entry we have used above requires that you know the
order of columns of the table. If you don't know or don't want to follow
the exact order of the columns, you can perform data entry with an order
of your choice. This allows you to provide the values of
fields in any order of your choice. We have just seen a few examples where the
values of some of the fields are not available during data entry. Instead
of remembering to type 0 or NULL for such fields or leaving empty quotes
for a fielde s you can use their names to specify the fields whose data you
want to provide.
To perform data entry at random, you must provide a
list of the fields 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. In the same way, you don't have to provide data for all
columns, just those you want, in the order you want. Here is an example:
INSERT Country(CountryName, Capital,InternetCode,Population,Area)
VALUES('Taiwan', 'Taipei', 'tw', 22548009, 35980)
Here is another example:
Country(InternetCode, CountryName, Capital, Area)
VALUES( 'mx', 'Mexico', 'Mexico', 1972550)
Instead of first creating a table and then performing data
entry, you can create a table and add records at once as long as you separate
the statements with GO. To proceed, in your code, you must first create
the table, which would save it, use GO to end the statement that creates
the table, start the statement or each statement used to add a record, and it or
each with GO. Consider the following example:
-- Database: VideoCollection
-- Table: Videos
IF EXISTS(SELECT name
WHERE name = N'Videos'
AND type = 'U')
DROP TABLE Videos
CREATE TABLE Videos (
INSERT INTO Videos VALUES('A Few Good Men', 'Rob Reiner', 1992, '138 Minutes', 'R')
INSERT INTO Videos(Director, VideoLength, VideoTitle, YearReleased)
VALUES('Jonathan Dame', '118 Minutes', 'The Silence of the Lambs', 1991)
INSERT INTO Videos(VideoLength, Rating, Director, VideoTitle)
VALUES('112 Minutes', 'R', 'Jonathan Line', 'The Distinguished Gentleman')
INSERT INTO Videos(Rating, VideoTitle, Director, VideoLength)
VALUES('R', 'The Lady Killers', 'Joel Coen & Ethan Coen', '104 Minutes')
INSERT INTO Videos VALUES('Ghosts of Mississipi', 'Rob Reiner', 1996, '130 Minutes', '')
To programmatically perform data entry using a SQL
statement, create an INSERT statement exactly following the descriptions made
for SQL Query Analyzer. Once the statement is ready, pass it as string to a SqlCommand
object and execute it with a call to SqlCommand::ExecuteNonQuery().