Home

Introduction to Records

  

Introduction

The records of a database are stored in tables. To visually add a record to a table, in the Object Explorer, expand the database and the Tables node. Right-click the table and click Edit Top 200 Rows. If no record exists in the table, it would appear with an empty row of cells marked NULL:

Table

To perform data entry on a table, you can click in a field and type the appropriate value.

To programmatically perform data entry, you use a Data Definition Language (DDL) command known as INSERT. 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);

An alternative is to add the INTO keyword after the INSERT keyword:

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.

The VALUES keyword is followed by parentheses. In the parentheses, type the desired values:

Adjacent Data Entry

To perform adjacent data entry, you must follow the sequence of fields of the table. Here is an example:

USE Exercise;
Go

CREATE TABLE Employees
(
    EmployeeNumber nchar(10),
    EmployeeName nvarchar(50),
    DateHired date,
    HourlySalary money
);
GO

INSERT INTO Employees
VALUES(N'593705', N'Frank Somah', N'20061004', 26.15);
GO

In the same way, you can create different INSERT sections for each record. Here is an example:

USE Exercise;
Go

INSERT INTO Employees
VALUES(N'204815', N'Jeanne Swanson', N'19980802', 18.48);
GO
INSERT INTO Employees
VALUES(N'824460', N'Ponce Valley', N'20041208', 22.25);
GO
INSERT INTO Employees
VALUES(N'495007', N'Gina Sow', N'20000622', 16.85);
GO

Instead of writing the INSERT expression for each record, you can write it once, followed by  VALUES. Then enter each record in its parentheses. The parentheses are separated by commas. Here is an example:

USE Exercise;
Go

INSERT INTO Employees
VALUES(N'595002', N'John Meah', N'20000212', 32.25),
      (N'928375', N'Chuck Stansil', N'20080628', 20.05),
      (N'792764', N'Orlando Perez', N'20000616', 12.95),
      (N'290024', N'Anne Nguyen', N'20090428', 25.75);
GO

Random Data Entry

To perform data entry in an order of your choice, provide a list of the fields of the table. Here is an example:

USE Exercise;
Go

CREATE TABLE Employees
(
    EmployeeNumber nchar(10),
    EmployeeName nvarchar(50),
    DateHired date,
    HourlySalary money
);
GO

INSERT INTO Employees(EmployeeNumber, EmployeeName, DateHired, HourlySalary)
VALUES(N'927957', N'Helen Gooding', N'19961220', 22.65);
GO

In the same way, you can create different INSERT sections for each record and each INSERT expression can have its own list of columns. Here are examples:

USE Exercise;
Go

INSERT INTO Employees(DateHired, EmployeeName, EmployeeNumber, HourlySalary)
VALUES(N'20081028', N'June Santos', N'729475', 24.85);
GO
INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired)
VALUES(N'Ann Pulley', N'300293', N'20020520');
GO
INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired)
VALUES(N'Annie Pastore', N'972405', N'19941122');
GO
INSERT INTO Employees(EmployeeNumber, HourlySalary)
VALUES(N'490007', 12.95);
GO

If you want to create a series of records that use the same sequence of fields, write the INSERT keyword or INSERT INTO expression, followed by the name of the table, followed by parentheses that contain the list of fields, and followed by  VALUES. Then enter each record in its parentheses. The parentheses are separated by commas. Here is an example:

USE Exercise;
Go

INSERT INTO Employees(EmployeeName, EmployeeNumber, DateHired)
VALUES(N'Clarice Simms', N'971403', N'20011112'),
      (N'Paul Handsome', N'720947', N'20000802'),
      (N'Gina Palau', N'247903', N'20080612');
GO

INSERT INTO Employees(DateHired, EmployeeName, EmployeeNumber, HourlySalary)
VALUES(N'20091124', N'Arnold Futah', N'222475', 22.75);
GO

Outputting the Insertion Result

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:

OUTPUT

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 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

Assistance With Data Entry: Using Expressions

 

Introduction

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.

Visually Creating an Expression

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:

Creating a SQL Expression

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
 

Using an Expression During Data Entry

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
     

  Copyright © 2009-2011 FunctionX.com