Transact-SQL Data Entry

In the SQL, data entry is performed using the INSERT combined with the VALUES keywords. The primary statement uses the following syntax:

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.

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 'D'.

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 Borland C++ Builder, Microsoft Visual Basic, or Visual C++, etc, that you can take care of this.

The most common technique of performing data entry requires that you know the sequence of fields 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 position.

During data entry on adjacent fields, if you don't have a value for a numeric field, you should type 0 as its value. For a string field whose data you don't have and cannot provide, type two single-quotes '' to specify an empty field.

1. To open the SQL Query Analyzer, in the SQL Server Enterprise Manager, click Tools -> SQL Query Analyzer
2. In the Query window, type:
    
3. Press F5 to execute
    
   
4. Delete the top section of the window
5. To perform another entry, enter and execute the following statement:
    

   INSERT Countries VALUES ( 'Angola', 1246700, 10593171, 'Luanda', 'ao' ) GO

6. To perform another entry, change the statement as follows and execute it:
    

   INSERT INTO Countries VALUES ( 'Mongolia', 1565000, 2694432, 'Ulaanbaator','mn' ) GO

7. To perform an entry with a NULL and an empty fields, change the statement as follows and execute it:
    

   INSERT INTO Countries VALUES('Trinidad and Tobago', NULL, 1163724, 'Port-of-Spain','') GO

8. To perform another entry with NULL fields, type and execute the following statement:
    

   INSERT INTO Countries VALUES('Spain', NULL, NULL, 'Madrid', '') GO

Random Data Entry

The adjacent data entry we have been performing requires that you know the position of each field. 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 columns in an 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 field, 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 fields, just those you want, in the order you want.

Practical Learning: Performing Random Data Entry

1. To perform our first random data entry, type and execute the following statement:
    

   INSERT Countries(CountryName, Capital,Code,Population,Area) VALUES('Taiwan', 'Taipei', 'tw', 22548009, 35980) GO

2. To perform another random data entry, type and execute the following statement:
    

   INSERT Countries(Code, CountryName, Capital, Area) VALUES( 'mx', 'Mexico', 'Mexico', 1972550) GO

3. Type and execute one more statement as follows:
    

   INSERT Countries(Population, CountryName, Code, Capital) VALUES( NULL, 'Cape Verde', '', 'Praia') GO

4. Type and execute this last statement:
    

   INSERT Countries(CountryName, Capital) VALUES('Tunisia', 'Tunis') GO

5. Return to SQL Server Enterprise Manager

Data Entry and the Identity Column

Introduction

One of the goals of a good table is to be able to uniquely identity each record. In most cases, the database engine should not confuse two records. Consider the following table:

Imagine that you want to find the record of Sweden or that of Honduras. This table appears to have two entries for Sweden and both display the exact same information. So, which record is valid and which one would be used? To eliminate or reduce this confusion, you should create one particular column whose values would be used to (uniquely) represent each record. In some cases, you can allow the user to create the values of that column. For example, you can create a column in which the user would enter the employee number of each employee, making sure that each employee has a unique number throughout the company. Suppose that, during data entry, the user is not able to create, generate or provide a number for an employee (may be it is difficult; for example, if the company already has 4122 employees and the user needs to create a unique number for the new employee, the clerk would need to check every number to make sure that the new one is still available. This can be difficult).

Instead of letting the user cre ate values of a column that uniquely identify each column, you can ask the interpreter to take care of this. In other words, the interpreter would create a new value for each new record. This type of column is called an identity column.

Practical Learning: Creating an Identity Column in the Design Table

1. In the right frame of the SQL Server Enterprise Manager, right-click the Countries table and click Design Table
2. Right-click anywhere in the CountryName line and click Insert Column
    
   
3. Set the Column Name as CountryID and its data type as int

Visually Creation of an Identity Column

Using the SQL Server Enterprise Manager, you can add an identity column when creating a brand new table or you can add it to an existing table. If the table exists already, you can right-click it in the right frame and click Design Table.

To create an identity column, first give it a name. By habits, the name of an identity column usually holds the singular name of the table and is appended with ID. For example, the identity column of an Employees table would be named EmployeeID. This is not a rule and is not enforced by the interpreter: it is only a suggestion.

After setting the name of the column, you can specify its data type. If you want the user to provide the unique values of the fields under that column, you can use any string-based or number-based data type of your choice. If you want the database interpreter to be responsible for generating the unique values of that column, you should set its data type to int.

After specifying the data type of the column, to actually indicate that the column will be managed as an identity column, you should set its Identity property to Yes. The default value is No, which would mean that the column is not used as an identifier. If the column is used as an identifier except when a replication is adding data to the table, you can set this property to Yes (Not For Replication).

Identity Seed: If you set the Identity property to Yes, you can specify how the value would change for the field. For example, if you set the Identity to Yes and the column's data type is an integer, you can use the Identity Seed property to specify what would be the first number counted. This allows you to ask the database to start counting the numbers at a value other than 1 (the default).

Identity Increment: If you specify the Identity property of a column as Yes, you can use the Identity Increment to specify how the counting would occur. By default, SQL Server would count numbers in increments of 1. An example would be 15, 16, 17, 18, etc. With a custom Identity Increment, you can use a number of 5 to increment as 15, 20, 25, 30, etc.

Practical Learning: Creating an Identity Column in the Design Table

1. In the lower section of the table, double-click the No value of its Identity property to change it from No to Yes
    
   
2. Accept the 1 values of the Identity Seed and the Identity Increment properties.
   Close the table
3. When asked whether you want to save the changes, click Yes
4. Switch to SQL Query Analyzer

SQL and the Identity Column

If you are programmatically creating a column, to indicate that it would be used as an identity column after its name and data type, type identity followed by parentheses. Between the parentheses, enter the seed and the increment as we reviewed above.

Practical Learning: Creating an Identity Column With SQL

1. On the toolbar of SQL Query Analyzer, click the arrow of the New Query button. Position the mouse on Create Table and click Create Table With IDENTITY Column
2. Change the code in the window as follows:
    

   -- ============================================= -- Database: WorldStats -- Table: Continents -- ============================================= USE WorldStats GO IF EXISTS (SELECT name FROM sysobjects WHERE name = N'Continents' AND type = 'U') DROP TABLE Continents GO create table Continents ( ContinentID int IDENTITY(1, 1) NOT NULL, ContinentName varchar(100) NOT NULL, Area varchar(20), Population varchar(20)) GO

3. Press F5 to execute the statement

Other Features of Data Entry

Is RowGuid

This property allows you to specify that a column with the Identity property set to Yes is used as a ROWGUID column.

Collation

Because different languages use different mechanisms in their alphabetic characters, this can affect the way some sort algorithms or queries are performed on data, you can ask the database to apply a certain language mechanism to the field by changing the Collation property. Otherwise, you should accept the default specified by the table.