Once a table contains information, you can review it using either SQL Server Management Studio or an external application.

The tables of a database display in the Object Explorer under their database node. To open a table for data entry, right-click it and click Edit Top 200 Rows.

Data Navigation consists of displaying and viewing data. Because information of a database is stored in tables, your primary means of viewing data consists of opening a table in a view that displays its information.

When a table displays its records, you navigate through its fields using the mouse or the keyboard. With the mouse, to get to any cell, you can just click it. To navigate through records using the keyboard, you can press:

• The right arrow key to move to the right cell; if the caret is already in the most right cell, it would be moved to the first cell of the next record, up to the last empty cell of the first empty record
• The left arrow key to move to the previous cell; if the caret is in, or reaches, the most left cell of the first record, nothing would happen when you press the the left arrow key
• The down arrow key to move to the cell under the current one; if the caret is already in the last cell of the current column, nothing would happen
• The up arrow key to move to the cell just above the current one; if the caret is already in the first cell of the current column, nothing would happen
• The Page Down to move to the next group of cell that would correspond to the next page; if the number of records is less than a complete page, the caret would move to the last cell of the current column
• The Page Up to move to the next group of cell that would correspond to the next page; if the number of records is less than a complete page, the caret would move to the first cell of the current column

As you are probably aware already, columns are used to organize data by categories. Each column has a series of fields under the column header. One of the actual purposes of a table is to display data that is available for each field under a particular column. Data entry consists of providing the necessary values of the fields of a table. Data is entered into a field and every time this is done, the database creates a row of data. This row is called a record. This means that entering data also self-creates rows.

There are four main ways you can perform data entry for a Microsoft SQL Server table:

• You can use a table from the Object Explorer
• You can enter data by typing code in a query window
• You can import data from another object or another database
• You can use an external application such as Microsoft Access, Microsoft Visual Basic, Borland C++ Builder, Microsoft Visual C++, Borland Delphi, Microsoft Visual Basic, C#, Visual C#, J#, etc.

Probably the easiest and fastest way to enter data into a table is by using SQL Server Management Studio. Of course, you must first open the desired table from an available database. In the Object Explorer, after expanding the Databases and the Tables nodes, open a table for data entry. If the table does not contain data, it would appear with one empty row:

If some records were entered already, their rows would show and the table would provide an empty row at the end, expecting a new record.

To perform data entry on a table, you can click in a field. Each column has a title, called a caption, on top. This gray section on top is called a column header. In SQL Server, it displays the actual name of the column. You refer to the column header to know what kind of data should/must go in a field under a particular column. This is why you should design your columns meticulously. After identifying a column, you can type a value. Except for text-based columns, a field can accept or reject a value if the value does not conform to the data type that was set for the column. This means that in some circumstances, you may have to provide some or more explicit information to the user.

1. Start Microsoft SQL Server, select the appropriate options in the Connect To Server dialog box and connect to the server
2. Right-click the server name and click New Query
3. To create a new database, in the empty window, type the following:
    

   CREATE DATABASE WorldStatistics; GO USE WorldStatistics; GO CREATE TABLE Countries ( [Country Name] NVARCHAR(50), Area INT, Population BIGINT, Capital NVARCHAR(50), [Internet Code] nchar(2) ); GO

4. To execute the SQL statement, press F5
5. Close the query window
6. When asked whether you want to save it, click No
7. In the Object Explorer, right-click Databases and click Start PowerShell
8. Type SQLCMD and press Enter
9. To create a new database, type the following and press Enter at the end of each line:
    

   USE master; GO IF EXISTS(SELECT name FROM sys.databases WHERE name = N'CeilInn1' ) DROP DATABASE CeilInn1 GO CREATE DATABASE CeilInn1 GO

10. Press Enter
11. Return to Microsoft SQL Server Management Studio
12. In the Object Explorer, right-click the Databases node and click Refresh. Expand the Databases node. 
    Under Databases, expand WorldStatistics and expand Tables
13. If you don't see a table named Countries, right-click the Tables node and click Refresh.
    Right-click Countries and click Edit Top 200 Rows
14. Click the first empty cell under CountryName, type Cote d'Ivoire and press Enter
15. Type 322460 for the area and press Tab
16. Type 16,393,221 and press Enter
17. Notice that you receive an error because the commas are not allowed:
     
    
18. Click OK on the error message box.
19. Change the value to 16393221 People and press Tab
20. Notice that you receive another error because the column is configured for a natural number and not a string
21. Click OK on the error message box and delete People
22. Under Internet Code, type ci and press Enter
23. Click the field under Capital, type Yamoussoukro and press Enter twice
24. Complete the table as follows:
    

    Country Name	Area	Population	Capital	Internet Code
    Cote d'Ivoire	322460	16393221	Yamoussoukro	ci
    Panama	78200	3191319	Panama	pa
    Australia	7686850	20264082	Canberra	au
    Canada	9984670	33098932	Ottawa	ca
    Iran	1648000	68688433	Tehran	ir

25. Close the table

To programmatically perform data entry, you use a Data Definition Language (DDL) command known as INSERT. To start, if you are working in Microsoft SQL Server:

• In the Object Explorer, you can right the table, position the mouse on Script Table As -> INSERT To -> New Query Editor Window. You may get code as follows:
   

  INSERT INTO [Exercise].[dbo].[Employees] ([EmployeeNumber] ,[LastName] ,[FirstName] ,[Username] ,[DateHired] ,[HourlySalary]) VALUES (<EmployeeNumber, int,> ,<LastName, nvarchar(20),> ,<FirstName, nvarchar(20),> ,<Username, nchar(8),> ,<DateHired, date,> ,<HourlySalary, money,>) GO

• Open an empty query window and type your code

The DDL command to perform data entry is INSERT combined with VALUES. The primary statement uses the following syntax:

INSERT TableName VALUES(Column1, Column2, Column_n);

Alternatively, or to be more precise, you can use the INTO keyword between the INSERT keyword and the TableName factor to specify that you are entering data into the table. 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 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.

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, C#, or MS 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 a new query window, press Ctrl + N
2. In the query window, to create one record, type:
    

   USE WorldStatistics; GO INSERT INTO Countries VALUES(N'Angola', 1246700, 12127071, N'Luanda', N'ao'); GO

3. Press F5 to execute
4. Delete the top section of the window
5. To enter various records at the same time, enter the following statement:
    

   USE WorldStatistics; GO INSERT INTO Countries VALUES(N'Mexico', 1972550, 107449525, N'Mexico City', N'mx'); (N'South Africa', 1219912, 44187637, N'Pretoria', N'za'); (N'Iraq', 0, 0, N'Baghdad', N'iq'); (N'United States', 9826630, 0, N'', N''); (N'Saudi Arabia', 2149690, 0, N'Riyadh', N''); GO

6. Press F5 to execute the statement

The adjacent data entry we have performed 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 columns in an order of your choice. We have just seen a few examples where the values of some of the fields were 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 the fields' names to specify the fields whose data you want to provide.

To perform data entry in an order of your choice, you must provide your list of the fields of the table. 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.

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

   USE WorldStatistics; GO INSERT Countries([Country Name],Capital,[Internet Code],Population,Area) VALUES(N'China', N'Beijing', N'cn', 1313973713, 9596960) GO

2. Press F5 to execute the statement
3. To perform other entries, type the following statement:
    

   USE WorldStatistics; GO INSERT Countries(Capital, [Internet Code], [Country Name]) VALUES(N'Nouakchott', N'mr', N'Mauritania') GO INSERT Countries([Internet Code], Population, [Country Name]) VALUES(N'ro', 22303552, N'Romania') GO INSERT Countries(Area, [Country Name], Population) VALUES(21040, N'El Salvador', 6822378) GO INSERT Countries(Capital, [Country Name]) VALUES(N'Phnom Penh', N'Cambodia') GO

4. To execute the statement, press F5
5. Close the query window
6. When asked whether you want to save it, click No

In previous sections, we added a single record to each use of the INSERT formula. You can add various records with one call to INSERT. If you are adding a value to each column of the table, after the name of the table, type VALUES, open and close the first parentheses. Inside the parentheses, include the desired values. To add another record, type a comma after the closing parenthesis, open a new parenthesis, list the new values, and close the parenthesis. Do this as many times as you need to add records. Here is an example:

-- =============================================
-- Database: Video Collection
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (SELECT name 
	    FROM sys.databases 
	    WHERE name = N'VideoCollection'
)
DROP DATABASE VideoCollection
GO

CREATE DATABASE VideoCollection
GO

-- =============================================
-- Database: Video Collection
-- Table:    Videos
-- =========================================
USE VideoCollection
GO

IF OBJECT_ID(N'Videos', N'U') IS NOT NULL
  DROP TABLE Videos
GO

CREATE TABLE Videos
(
	Title nvarchar(50), 
	Director nvarchar(50), 
	Rating nchar(10),
	YearReleased int
)
GO

INSERT INTO Videos
VALUES(N'Her Alibi', N'Bruce Beresford', N'PG-13', 1998),
      (N'Memoirs of a Geisha', N'Rob Marshall', N'PG-13', 2006),
      (N'Two for the Money', N'D.J. Caruso', N'R', 2008);
GO

This is valid for adjacent data entry. If you want to follow your own order of columns, on the right side of the name of the table, include a list of columns in parentheses. Then, when giving the values, for each record, follow the order in which you listed the columns. Here is an example:

-- =============================================
-- Database: Video Collection
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS(SELECT name 
	   FROM sys.databases 
	   WHERE name = N'VideoCollection'
)
DROP DATABASE VideoCollection
GO

CREATE DATABASE VideoCollection
GO

-- =============================================
-- Database: Video Collection
-- Table:    Videos
-- =========================================
USE VideoCollection
GO

IF OBJECT_ID(N'Videos', N'U') IS NOT NULL
  DROP TABLE Videos
GO

CREATE TABLE Videos
(
	Title nvarchar(50), 
	Director nvarchar(50), 
	Rating nchar(10),
	YearReleased int
)
GO

INSERT INTO Videos(Rating, Title, Director)
VALUES(N'R', N'Wall Street', N'Oliver Stone'),
      (N'', N'Michael Jackson Live in Bucharest', N'Andy Morahan'),
      (N'PG-13', N'Sneakers', N'Paul Alden Robinson'),
      (N'R', N'Soldier', N'Paul Anderson');
GO

In the techniques we have used so far, when or if the records have been added to a table, whether the operation was successful or not, we had no way of finding out. One way you can get this information is to store the inserted records in another table. To support this, Transact-SQL provides the OUTPUT operator. The formula to use it is:

INSERT INTO TableName
OUTPUT INSERTED.Columns
VALUES(Value_1, Value_2, Value_X)

You start with the normal record insertion with the INSERT INTO TableName expression. This is followed by the OUTPUT operator followed by the INSERTED operator and a period. If you are adding a value for each record, follow the period with *. The statement continues with the VALUES operator that is followed by parentheses in which you list the values to be added to the table. Here is an example:

USE VideoCollection;
GO

CREATE TABLE Videos
(
	Title nvarchar(50), 
	Director nvarchar(50),
	WideScreen bit,
	Rating nchar(10),
	YearReleased int
)
GO

INSERT INTO Videos
OUTPUT inserted.*
VALUES(N'War of the Roses (The)', N'Dany de Vito', 0, N'R', 2001),
      (N'Memoirs of a Geisha', N'Rob Marshall', 1, N'PG-13', 2006),
      (N'Last Castle (The)', N'Rod Lurie', 1, N'', 2001),
      (N'Sneakers', N'Phil Alden Robinson', 1, N'PG-13', 2003);
GO

When this statement executes, if you are working in the Microsoft SQL Server Management Studio, the lower part would display a list of the records that were added:

If you use the above formula, when you close the database, the reference is lost. If you want to store the list of newly created records in a table, on the right side of the INSERTED operator and its period, type INTO followed by the name of the table that will receive the values. The table must have been created; that is, it must exist at the time this inserted operation is taking place. Here is an example:

USE VideoCollection;
GO

CREATE TABLE Archives
(
	Title nvarchar(50), 
	Director nvarchar(50),
	WideScreen bit,
	Rating nchar(10),
	YearReleased int
)
GO

INSERT INTO Videos
OUTPUT inserted.* INTO Archives
VALUES(N'Two for the Money', N'D.J. Caruso', 1, N'R', 2006),
      (N'Wall Street', N'Oliver Stone', 0, N'R', 2000);
GO

In this case, a copy of the newly created records would be stored in the indication table.

The above techniques assume that you are adding a complete record; that is, you are providing a value for each column of the table. We already saw that if you want to provide values for only some columns, after the name of the table, provide the list of columns in parentheses. To get the list of newly inserted records, after the OUTPUT keyword, type INSERTED followed by a period and followed by the name of the first column. Do this for each column. The formula to use is:

INSERT INTO TableName(Column_1, Column_2, Column_X)
OUTPUT INSERTED.Column_1, INSERTED.Column_2, INSERTED.Column_X
VALUES(Value_1, Value_2, Value_X)

Of course, you can list the columns in an order of your choice, as long as both the TableName and the OUTPUT section use the exact same order. Here is an example:

USE VideoCollection;
GO

INSERT INTO Videos(Director, Rating, Title)
OUTPUT inserted.Director, inserted.Rating, inserted.Title
VALUES(N'Jonathan Lynn', N'R', N'Distinguished Gentleman (The)'),
      (N'Paul Anderson', N'R', N'Soldier');
GO

In this case, when the statement has executed, the result would display in the lower portion of the Microsoft SQL Server Management Studio. If you want to store the result in a table, use the following formula

INSERT INTO TableName(Column_1, Column_2, Column_X)
OUTPUT INSERTED.Column_1, INSERTED.Column_2, INSERTED.Column_X INTO TargetTable
VALUES(Value_1, Value_2, Value_X)

Here is an example:

USE VideoCollection;
GO

CREATE TABLE Entertainment
(
	Title nvarchar(50), 
	Director nvarchar(50)
)
GO

INSERT INTO Videos(Title, Director)
OUTPUT inserted.Title, inserted.Director INTO Entertainment
VALUES(N'Michael Jackson Live in Bucharest', N'Andy Morahan'),
      (N'Outfoxed', N'Robert Greenwald');
GO

There are various ways you can assist the user with data entry. Besides using a function, you can create an expression using operators such as those we reviewed in lessons 3 and 5. You can create an expression when creating a table, whether in the Table window or using SQL in a query window.

To create an expression when visually creating a table, in the top section, specify the column's name (only the column name is important). In the bottom section, expand the Computed Column Specification field and, in its (Formula) field, enter the desired expression. Here is an example:

You can also create an expression in SQL expression you are using to create a table. To do this, in the placeholder of the column, enter the name of the column, followed by AS, and followed by the desired expression. Here is an example:

CREATE TABLE Circle
(
    CircleID int identity(1,1) NOT NULL,
    Radius decimal(8, 3) NOT NULL,
    Area AS Radius * Radius * PI()
);
GO

When performing data entry, you must not provide a value for a column that has an expression; the SQL interpreter would provide the value automatically. Here is an example of entering data for the above Circle table:

INSERT INTO Circle(Radius) VALUES(46.82);
GO
INSERT INTO Circle(Radius) VALUES(8.15);
GO
INSERT INTO Circle(Radius) VALUES(122.57);
GO