Home

Introduction to Records

 

Records Fundamentals

 

Introduction

A table is an object that holds the information of a database. Because a table is the central part of a database, the information it holds must be well organized. To better manage its information, data of a table is arranged in a series of fields.

The tables of a database display in the Object Explorer under their database node.

Assistance With Data Entry

We continue with our university database. To run its many services, the school has a managing team that consists of a president, deans, and other employees.

In this new version of the database, University3, we will create a table for employees. The table will be stored in the Academics schema.

   

Practical LearningPractical Learning: Introducing Data Entry

  1. Start the computer and log in
  2. Launch Microsoft SQL Server and click Connect
  3. Right-click the server name and click New Query
  4. To create a new database, in the empty window, type the following:
    USE master;
    GO
    DROP DATABASE University1;
    GO
    CREATE DATABASE University2;
    GO
    USE University2;
    GO
    CREATE SCHEMA Academics;
    GO
    CREATE TABLE Academics.StudentsGradeScale
    (
    	LetterGrade char
    );
    GO
  5. To execute the SQL statement, press F5
  6. In the Object Explorer, right-click the Databases node and click Refresh. Expand the Databases node
  7. Expand the University2 node
  8. Expand its Tables node

Table Data Navigation in the SQL Server Management Studio

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
Introduction to Records
  • 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

Practical LearningPractical Learning: Introducing Data Navigation

  • Under University2, right-click Academics.StudentsGradeScale and click Edit Top 200 Rows

Fundamentals of Data Entry

 

Introduction

As you are probably aware already, columns are used to organize data by categories. Each column has a series of cells 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.

Fundamentals of Data Entry

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 a row.

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 Editor
  • You can import data from another object or another database
  • You can use an external application such as Microsoft Access, Microsoft Visual Basic, Embarcadero C++Builder, Microsoft Visual C++, Embarcadero Delphi, Microsoft Visual Basic, C#, Microsoft Visual C#, etc
 

Visual Data Entry

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:

Table

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 cell. Each column has a title, called a caption, on top. This gray section on top is called a column header. In Microsoft SQL Server, it displays the actual name of the column.  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.

Practical LearningPractical Learning: Creating a Record

  1. Under the LetterGrade header, click NULL and type A
  2. Close the table

Programmatic Data Entry

To programmatically perform data entry, you use a Data Definition Language (DDL) command. To start, if you are working in the SQL Server Management Studio:

  • In the Object Explorer, you can right-click 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 Editor 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 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)

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

Data Entry Based on the Type of Value: Characters and Strings

   

Introduction

To provide the value of a character or string in the Table window, simply type the desired value. If you are the value using SQL, include the value  between single-quotes.

The Length of a String

We saw that, when creating a column that would hold text, characters, or any combination of symbols, you can specify its data type as char. In reality, when you indicate that a column would hold characters, the database engine wants you to specify the (maximum) number of characters the column, or the field, would hold. This number of characters is also called the length of a string.

If you are visually creating the table, after specifying its name under Column Name, select char (or any data type that has char in its name) in the corresponding Data Type combo box:

Characters

As you can see, the default lengh is set to 10. If you want to change:

  • In the top section of the table, you can directly type it in the parentheses of char() in the Data Type combo box
  • In the top section of the table, click the column. In the lower section of the Table window, click Length and type the desired value:

The Number of Characters

Unicode Characters

If you apply the char data type to a column, it can hold a maximum of 8000 characters. Also, that data type is meant to support only characters of Latin-based languages. To let your column support characters and symbols outside the Latin-based languages, Transact-SQL provides a data type named nchar.

If you apply the nchar data type to a column, when performing data entry using SQL, if you want to indicate that the value in single-quotes must be treated to support all types of characters, precede the first single-quote with N. Here is an example:

CREATE TABLE Contractors
(
	Gender char
);
GO
INSERT INTO Contractors VALUES(N'M');
GO

Fixed and Variable Lengths of Strings

If you apply char or nchar to a column, remember that you must specify the maximum number of characters, such as 10. If you do this, the database engine reserves memory space for exactly that amount. If you store fewer characters than the length you specified, the other rest of memory would be left empty and nothing else would be stored in that empty space. This means that char and nchar use a fixed length of memory. This is fine if you know for sure that all values of the columns would have the same length. Examples are the shelf numbers of a library, the employees' numbers, etc. In some or most cases, the values of a string-based column change from one record to another. For such a column, you should apply a string data type whose length can change.

To support variable strings, Transact-SQL provides the varchar data type. When performing data entry, provide the value in single-quotes. To support international characters, Transact-SQL provides the nvarchar data type. If you apply any of these data types, the field can store up to 231 characters. In both cases, the database engine doesn't assume that you will use all the space that was allocated. If fact, you should indicate how much space your variable will need by providing a number in the type's the parentheses. In the parentheses, enter the desired number. Here are examples:

Fixed and Variable Lengths of Strings
CREATE TABLE Presidents
(
    FirstName nvarchar(24),
    LastName nvarchar(24)
);
GO
INSERT INTO Presidents VALUES(N'Julius', N'Nyerere');
GO

Practical LearningPractical Learning: Changing a Column's Type

  1. Click inside the Query Editor and press Ctrl + A to select everything
  2. Type the following:
    ALTER TABLE Academics.StudentsGradeScale
    ALTER COLUMN LetterGrade nvarchar(5);
    GO
  3. Right-click inside the Query Editor and click Execute
  4. In the Object Explorer, under University2, expand Academics.StudentsGradeScale. If necessary, right-click the Columns node under the Tables and click Refresh

    Changiing a Column

  5. To add a new column, modify the code in the Query Editor as follows:
    ALTER TABLE Academics.StudentsGradeScale
    ADD Descriptor nvarchar(20);
    GO
  6. Right-click inside the Query Editor and click Execute
  7. In the Object Explorer, right-click Academics.StudentsGradeScale and click Edit Top 200 Rows
  8. Complete the columns with the following values:
     
    Letter Grade Descriptor
    A Excellent
    A- Excellent
    B+ Good
    B Good
    B- Good
    C+ Satisfactory
    C Satisfactory
    C- Satisfactory
    D+ Satisfactory
    D Satisfactory
    F Unsatisfactor

    Tables Records

  9. Close the table

Text

Text is considered a group of strings, lines, or paragraphs that must be stored beyond the amount of memory of a normal string. To store such text, Transact-SQL provides many alternatives.

Transact-SQL originally supported a data type named text (and ntext for Unicode) used for long text. A better alternative is to use either varchar() or nvarchar() and specify the length using the max keyword. Here is an example:

CREATE TABLE Sports
(
    SportName nvarchar(50),
    [Description] nvarchar(max)
);
GO

In this case, varchar(max) or nvarchar(max) is called a large-value data type.

Data Entry With Natural Numbers

 

Natural Numbers Types

If a column's type is an integer, to specify its value, whether working visually or in code, simply provide its value as the number. Here are examples:

CREATE TABLE MaritalsStatus
(
    StatusCode tinyint,
    MaritalsStatus nvarchar(32)
);
GO

INSERT INTO MaritalsStatus VALUES(1, N'Single');
GO
INSERT INTO MaritalsStatus VALUES(2, N'Married');
GO

Remember that a natural number can hold a negative or a positive value. A negative value must start with a - sign. When a number is (very) long and becomes difficult to read, such as 79435794, you are allowed to type a symbol called the thousand separator in each thousand increment. An example is 79,435,794. For your data entry in SQL, never include the thousand separator: you would receive an error.

When specifying the value of a number-based column, you must respect the storage amount of the data type applied to the column, providing values neither below nor beyond the allowed range. Otherwise you would receive an error.

Practical LearningPractical Learning: Adding Numeric Values

  1. In the Object Explorer, right-click Academics.StudentGradeScale and click Design
  2. Right-click Descriptor and click Insert Column
  3. Set the new column as follows:
    Column Name: MinimumPercent
    Data Type: tinyint
  4. Right-click Descriptor and click Insert Column
  5. Set the new column as follows:
    Column Name: MaximumPercent
    Data Type: tinyint

    Adding Numeric Values
  6. Close the table
  7. When asked whether you want to save, click Yes (if you receive an error, click Cancel, and allow changes)
  8. In the Object Explorer, right-click Academics.StudentsGradeScale and click Edit Top 200 Rowns
  9. Create new values as follows:
    Letter Grade MinimumPercent MaximumPercent Descriptor
    A 95 100 Excellent
    A- 90 94 Excellent
    B+ 85 89 Good
    B 80 84 Good
    B- 75 79 Good
    C+ 70 74 Satisfactory
    C 65 69 Satisfactory
    C- 60 64 Satisfactory
    D+ 55 59 Satisfactory
    D 50 54 Satisfactory
    F 0 49 Unsatisfactor
  10. Close the table

Binary Integers

When providing the value of a column that is of binary type, type its as a normal integer.

Data Entry With Decimal Numbers

   

Decimal Number-Based Columns

If you apply the numeric or the decimal data type to a column, it functions approximately like an integer-based column. This means that you can specify its value as a natural number. Here are examples:

CREATE TABLE Distances
(
    FromCity nvarchar(40),
    ToCity nvarchar(40),
    Distance decimal
);
GO

INSERT INTO Distances VALUES(N'Baltimore, MD', N'Richmond, VA', 130);
GO
INSERT INTO Distances VALUES(N'New York, NY', N'Las Vegas, NV', 2232);
GO

This also means that you can use this decimal type where you would use an integer.

Decimal Numbers and Precision

A precision is the number of digits used to display a numeric value. For example, the number 42005 has a precision value of 5, while 226 has a precision of 3. If the data type is specified as an integer (the int and its variants), the precision is fixed by the database and you can just accept the value set by the Microsoft SQL Server interpreter. For a decimal number (decimal or numeric data types), you have the option of specifying or not indicating a precision. If you don't specify a precision, the decimal and numeric data types are treated as integer. This means that if you provide a fractional part, the number would be converted to integer. For example, 3.33 would be converted to 3 and 3.67 would be converted to 4. Here are examples:

CREATE TABLE StudentsGradeScale
(
	LetterGrade nvarchar(2),
	MinRange decimal,
	MaxRange decimal,
	MinPercent decimal,
	MaxPercent decimal,
	Descriptor nvarchar(20)
);
GO
INSERT INTO StudentsGradeScale(LetterGrade, MinRange, MinPercent, MaxPercent, Descriptor)
VALUES(N'A', 4.0, 95, 100, N'Excellent');
GO
INSERT INTO StudentsGradeScale VALUES(N'A-', 3.67, 3.99, 90, 94, N'Excellent');
GO
INSERT INTO StudentsGradeScale VALUES(N'B+', 3.33, 3.66, 85, 89, N'Good');
GO
INSERT INTO StudentsGradeScale VALUES(N'B', 3.0, 3.32, 80, 84, N'Good');
GO
INSERT INTO StudentsGradeScale VALUES(N'B-', 2.67, 2.99, 75, 79, N'Good');
GO
INSERT INTO StudentsGradeScale VALUES(N'C+', 2.33, 2.66, 70, 74, N'Satisfactory');
GO
INSERT INTO StudentsGradeScale VALUES(N'C', 2.0, 2.32, 65, 69, N'Satisfactory');
GO
INSERT INTO StudentsGradeScale VALUES(N'C-', 1.67, 1.99, 60, 64, N'Satisfactory');
GO
INSERT INTO StudentsGradeScale VALUES(N'D+', 1.33, 1.66, 55, 59, N'Satisfactory');
GO
INSERT INTO StudentsGradeScale VALUES(N'D', 1.0, 1.32, 50, 54, N'Satisfactory');
GO
INSERT INTO StudentsGradeScale VALUES(N'F', 0, 0.99, 0, 49, N'Unsatisfactor');
GO

This would produce:

Letter Grade Min Range Max Range Min % Max % Descriptor
A 4   95 100 Excellent
A- 4 4 90 94 Excellent
B+ 3 4 85 89 Good
B 3 3 80 84 Good
B- 3 3 75 79 Good
C+ 2 3 70 74 Satisfactory
C 2 2 65 69 Satisfactory
C- 2 2 60 64 Satisfactory
D+ 1 2 55 59 Satisfactory
D 1 1 50 54 Satisfactory
F 0 1 0 49 Unsatisfactor

Microsoft SQL Server allows you to specify the amount of precision you want. The value must be an integer between 1 and 38 (28 if you are using Microsoft SQL Server 7). To specify the precision of a decimal or numeric-based column, add the parentheses after its data type and enter the number you want. Here is an example:

/*
 Imagine a credit counseling company that assists people 
 with some financial discipline issues.
 This table is used to keep track of credit card rates
 and certificates of deposits rates of various banks to direct 
 customers as to where to apply to get good rates or better ROI.
*/
CREATE TABLE BanksRates
(
	TypeOfCredit nvarchar(40),
	InstitutionName nvarchar(50),
	InterestRate decimal(6),
	CompoundFrequency nvarchar(32)
);
GO

INSERT INTO BanksRates
VALUES(N'Certificate of Deposit', N'Leandro Investments', 15.984, N'Monthly');
GO

The Scale of a Decimal Number

A decimal number is a number that has a fractional section. Examples are 12.05 or 1450.4227. The scale of a number is the number of digits on the right side of the period (or the character set as the separator for decimal numbers for your language, as specified in Control Panel). The scale is used only for numbers that have a decimal part, which includes currency (money and smallmoney) and decimals (numeric and decimal). If a column is using a decimal or numeric data type, you can specify the amount of scale you want. The value must be an integer between 0 and 18.

To specify the scale of a decimal or numeric-based column, in the parentheses if the data type, add a precision, followed by a comma, and followed by the desired scale.  This time, when performing data entry, you can provide a fractional part to the value of the column. Here are examples:

DROP TABLE StudentsGradeScale;
GO
CREATE TABLE StudentsGradeScale
(
	LetterGrade nvarchar(2),
	MinRange decimal(5, 2),
	MaxRange decimal(5, 2),
	MinPercent decimal,
	MaxPercent decimal,
	Descriptor nvarchar(20)
);
GO

Practical LearningPractical Learning: Adding Decimal Values

  1. In the Object Explorer, right-click Academics.StudentGradeScale and click Design
  2. Right-click MinimumPercent and click Insert Column
  3. Set the new column as follows:
    Column Name: MinimumRange
    Data Type:       decimal(18, 0)
    Precision:         5
    Scale:               2
  4. Right-click MinimumPercent and click Insert Column
  5. Set the new column as follows:
    Column Name: MaximumRange
    Data Type:       decimal(18, 0)
    Precision:         5
    Scale:               2

    Adding Numeric Values

  6. Close the table
  7. When asked whether you want to save, click Yes
  8. In the Object Explorer, right-click Academics.StudentsGradeScale and click Edit Top 200 Rowns
  9. Create new values as follows:
     
    Letter Grade Min Range Max Range Min % Max % Descriptor
    A 4   95 100 Excellent
    A- 3.67 3.99 90 94 Excellent
    B+ 3.33 3.66 85 89 Good
    B 3 3.32 80 84 Good
    B- 2.67 2.99 75 79 Good
    C+ 2.33 2.66 70 74 Satisfactory
    C 2 2.32 65 69 Satisfactory
    C- 1.67 1.99 60 64 Satisfactory
    D+ 1.33 1.66 55 59 Satisfactory
    D 1 1.32 50 54 Satisfactory
    F 0 .99 0 49 Unsatisfactory

    Tables Records

  10. Close the table

Real Numeric Types

A floating-point number is a fractional number, like the decimal type. Floating-point numbers can be used if you would allow the database engine to apply an approximation to the actual number. To declare such a variable, use the float or the real keyword.

As for integer-based columns (the int and its variants), the precision of floating-point numbers (float and real) is fixed. This means that the database interpreter keeps the fraction part you specify for the value.

Practical LearningPractical Learning: Using Real Values

  1. In the Object Explorer, right-click Databases and click New Database
  2. Set the name to FurnitureStore1
  3. Click OK
  4. In the Object Explorer, expand FurnitureStore1
  5. Right-click its Tables node and click New Table...
  6. Create a column as follow:
    Column Name: CommissionRate
    Date Type:       real
    Precision:         5
    Scale:               2
  7. Close the table
  8. When asked whether you want to save it, click Yes
  9. Set the name to EmployeesCommissions
  10. Click OK
  11. In the Object Explorer, refresh the Tables node of FurnitureStore and expand the Tables node
  12. Right-click EmployeesCommission and click Edit Top 200 Rows
  13. Create the following records:
     
    Commission Rate
    1.4
    .54
    .28
    .1875
    .09
    .0075
  14. Close the table

Currency Values

If a column is using the money or the smallmoney data type, when performing data entry, simply provide the value of the column. Remember, a money-based column can store values between -922,337,203,685,477.5808 and +922,337,203,685,477.5807.  A smallmoney-based column can store values between -214,748.3648 and 214,748.3647.

The precision and scale of a money or smallmoney variable are fixed by Microsoft SQL Server. The scale is fixed to 4.

Practical LearningPractical Learning: Adding Currency Values

  1. In the Object Explorer, right-click FurnitureStore1 and click Design
  2. Right-click CommissionRate and click Insert Column
  3. Set the new column as follows:
    Column Name: TransactionMinimum
    Data Type:         money
  4. Right-click CommissionRate and click Insert Column
  5. Set the new column as follows:
    Column Name: TransactionMaximum
    Data Type:         money
  6. Right-click CommissionRate and click Insert Column
  7. Set the new column as follows:
    Column Name: CommissionBase
    Data Type:         money
  8. Close the table
  9. When asked whether you want to save, click Yes
  10. Right-click EmployeesCommission and click Edit Top 200 Rows
  11. Create the following records:
     
    Mininimum Transaction Maximum Transaction Commission Base Commission Rate
    0 2499 26.25 1.4
    2500 5999 45 0.54
    6000 19999 60 0.28
    20000 49999 75 0.1875
    50000 499999 131.25 0.09
    500000   206.25 0.0075
  12. Close the table

Boolean Values

In Transact-SQL, Boolean values use the BIT (or bit) data type for values that can be set to True or False. When providing a value for such a column, specify the value as 1 for True or 0 for False.

 
 
 

Data Entry With Other Data Types

   

Introduction

Transact-SQL supports various data types, some of which we have seen so far. One of the types we have not used so far is for dates. While we will come back to it some other time, the simplest way to use a date or a time value is to create a string-based column using char or its variants. Make sure you specify a length for the data type.

After creating a column that uses characters, to specify a date value during data entry, follow the rules you are familiar with. The basic formula to use is:

MonthNumber-DayNumber-YearNumber
Data Entry With Other Data Types

 

The value is provided as a string. An example is N'06-24-2012'. This represents June 24, 2012.

In the same way, to specify a time value, follow the rules for time values. The formula to use is:

Hour:Minute[:OptionalSeconds]

An example is N'08:24'. Another example is N'10:44 PM'. One more example is N'19:36'.

A SQL Variant

Transact-SQL provides the sql_variant data type that can be used in place of the other types we had used so far. After specifying it as a column's data type, to perform data entry, use the appropriate format depending on the type, whether it is a string, a number, or a date, etc. Here are examples:

USE Exercise;
GO

CREATE TABLE SeasonalWorkers
(
    FirstName SQL_VARIANT,
    LastName sql_variant,
    DateHired Sql_Variant,
    IsMarried SQL_variant,
    YearlyIncome sql_variant
);
GO
INSERT INTO SeasonalWorkers(FirstName, LastName, DateHired, IsMarried, YearlyIncome)
VALUES(N'Paul', N'Yamo', N'04-07-2012', 1, 48500.15);
GO

Data Entry With Spatial Types

Transact-SQL provides the geometry data type to support for geometric coodinates. Unlike the other data types, the geometry type is in fact a type of object (in an object-oriented language, we would call it a class). This means that it has characteristics (called properties) and actions (called member functions or methods; a method is a function created as a member of a class). The characteristics of the geometry type are defined by the Open Geospatial Consortium (OGC). To adapt the data type to Transact-SQL, Microsoft added some functionalities to the type.

After specifying geometry as the data type of a column, you can set its values. The most fundamental action is to specify its value. To support this, the class is equipped with a method named STGeomFromText. Its syntax is:

static geometry STGeomFromText('geography_tagged_text', SRID)

The static keyword indicates that this is a static method. This means that, to access it, you use geometry::STGeomFromText. This method returns a value or type geometry.  Here is an example:

USE Exercise;
GO

CREATE TABLE PictureLocation
(
    PictureName nvarchar(50),
    Coordinate geometry
);
GO
INSERT INTO PictureLocation(PictureName, Coordinate)
VALUES(N'IMG001.jpg', geometry::STGeomFromText(. . .);
GO

This method takes two arguments. The first argument holds a value identified as a Well-Known Text (WKT) value. The value follows a format defined by OGC. There are various ways you can specify this value. As you may know already, a geometric point is an object that has two values: the horizontal coordinate x and the vertical coordinate y. The value can be integers or flowing-point numbers.

If you know the coordinates of a point and you want to use it as the value of the geometry object, type point() (or POINT(), this is not case-sensitive) and, in the parentheses, type both values separated by a space. Here is an example:

geometry::STGeomFromText('point(6 4)', . . .);

Instead of just one point, you may want to use a geometric value that is a line. In this case, specify the shape as linestring(, ). In the parentheses and on both sides of the comma, type each point as x and y. Here is an example:

geometry::STGeomFromText('linestring(1 4, 5 2)', . . .);

You can also use a complex geometric value, in which case you can pass the argument as a polygon. Use polygon(()) (or POLYGON(())) and pass the vertices in the parentheses. Each vertext should specify its x and y coordinates. The vertices are separated by commas. A last vertex should be used to close the polygon, in which case the first and the last vertices should be the same. Here is an example:

geometry::STGeomFromText('polygon((1 2, 2 5, 5 5, 4 2, 1 2))', . . );

The second argument of the  geometry::STGeomFromText method is a contant integer known as the spatial reference ID (SRID). Here is an example:

USE Exercise;
GO

CREATE TABLE PictureLocation
(
    PictureName nvarchar(50),
    Coordinate geometry
);
GO
INSERT INTO PictureLocation(PictureName, Coordinate)
VALUES(N'IMG001.jpg', geometry::STGeomFromText('point(6 4)', 0));
GO

Setting the Value of a User-Defined Type Column

After creating a UDT and setting it as the data type of a column, you can specify its value. Here are examples:

USE Exercise;
GO

CREATE TYPE NaturalNumber FROM int;
GO
CREATE TYPE ShortString FROM nvarchar(20);
GO
CREATE TYPE ItemCode FROM nchar(10);
GO
CREATE TYPE LongString FROM nvarchar(80);
GO
CREATE TYPE Salary FROM decimal(8, 2);
GO
CREATE TYPE Boolean FROM bit;
GO
CREATE TABLE Employees
(
    EmployeeID NaturalNumber,
    EmployeeNumber ItemCode,
    FirstName ShortString,
    LastName ShortString,
    [Address] LongString,
    HourlySalary Salary,
    IsMarried Boolean
);
GO
INSERT INTO Employees(EmployeeID,EmployeeNumber,FirstName,
                      LastName,[Address],HourlySalary,IsMarried)
VALUES(1, N'28-380', N'Gertrude', N'Monay', N'1044 Alicot Drive', 26.75, 1);
GO

Of course, you can mix Transact-SQL data types and your own defined type in your code.

Techniques of Performing Data Entry

 

Adjacent Data Entry

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.

Random Data Entry

Random Data Entry

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.

 

Creating Multiple Records

In previous sections, we added a single record to each use of the INSERT operation. 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:

CREATE DATABASE VideoCollection
GO
USE VideoCollection
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.

Inserting a Number or Percentage of Records

Imagine you have a series of records and you want to add them to a table. Transact-SQL allows you to specify whether to insert all of the records, a certain number of records, or a portion of the records.

Based on a number of records, to insert a fraction of the records, after the INSERT keyword, type TOP (Number) followed by the name of the table and the rest of the formula we have used so far. Here is an example:

USE VideoCollection
GO
INSERT TOP (2) INTO Videos(Rating, Title, Director)
VALUES(N'PG-13', N'Big Momma''s House ', N'Raja Gosnell'),
      (N'G', N'Annie', N'John Huston'),
      (N'PG', N'Incredibles (The)', N'Brad Bird'),
      (N'PG-13', N'Mission: Impossible', N'Brian De Palma'),
      (N'R', N'Negotiator (The)', N'F. Gary Gray');
GO

This code instructs the database engine to insert only 2 records from the list, regardless of the number of records that are provided.

Instead of specifying a fixed number of records, you can ask the database engine to insert a certain percentage of records. In this case, after TOP (Number), add the PERCENT word. Here is an example:

USE VideoCollection1
GO
INSERT TOP (40) PERCENT INTO Videos(Rating, Title, Director)
VALUES(N'', N'Professionals (The)', N'Richard Brooks'),
      (N'R', N'Trading Places', N'John Landis'),
      (N'PG-13', N'Cellular', N'David R. Ellis'),
      (N'R', N'Negotiator (The)', N'F. Gary Gray'),
      (N'PG-13', N'Big Momma''s House ', N'Raja Gosnell'),
      (N'G', N'Annie', N'John Huston');
GO

The code provides six records but asks the database engine to add 40% of them. That is 6 / (100/40) = 6 / 2.5 = 2.4. The closest higher integer to this number is 3. Therefore, 3 records are added.

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 immediately finding out (we would have to open the table). 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 VideoCollection1;
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 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 insertion operation is taking place. Here is an example:

USE VideoCollection1;
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 record(s) would be stored in the indicated 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 and separate them with commas. 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 any 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

Other Techniques of Getting Databases

 

Introduction

One of the techniques used to get data into one or more tables consists of importing already existing data from another database or from any other recognizable data file. Microsoft SQL Server provides various techniques and means of getting or importing data.

Using a Script

A script is a regular text-based file. In Microsoft SQL Server, the file should have the extension .sql. The script can have any type of code that the database engine can execute. That is, a Transact-SQL script can have any of the topics we will study throughout our lessons.

Using a script in Microsoft SQL Server is usually simple. Probably the easiest way to use a script is to open it as a file in the SQL Server Management Studio (you open the file like any other). Once it is opened, you can execute it. An alternative is to execute a file at the command prompt, in which case you can use either PowerShell or the DOS Command Prompt. To do this, at the prompt, use the following formula:

SQLCMD -i Filename

You start with the SQLCMD application and add the -i flag. This is followed by either only the name of the file or the complete path of the file. Of course, the file name must have the .sql extension.

Practical LearningPractical Learning: Executing a SQL Script

  1. On the desktop's taskbar, click Start. In the text box, type cmd and press Enter
  2. At the Command Prompt, type PowerShell and press Enter (an alternative is to click Start -> (All) Programs -> Accessories -> Windows PowerShell -> Windows PowerShell)
  3. To create a directory for this project, type:
    New-Item 'C:\Red Oak High School' -type directory
  4. Press Enter:
     
    Creating a Directory
     
    (if that doesn't work for any reason, then use a file utility, such as Windows Explorer, to create a directory named Red Oak High School in a drive or folder of your choice, but make sure you remember where you store it)
  5. Copy the rosh.sql file (rosh.txt) to the new your Red Oak High School directory
  6. Return to the command prompt of the PowerShell window
  7. Type SQLCMD -i 'C:\Red Oak High School\rosh.sql'
     
    Creating the Database
  8. Press Enter

Importing a Microsoft Access Database

It is possible to import a Microsoft Access database but it is easier if the file is in the .mdb format.

Practical LearningPractical Learning: Importing a Microsoft Access Database

  1. In the SQL Server Management Studio, right-click the Databases node and click New Database...
  2. Type Cruise1
  3. In the Path column, click each browse button and select the C:\Microsoft SQL Server Database Development folder
  4. Click OK
  5. In the Object Explorer, right-click Cruise1, position the mouse on Tasks and click Import Data
     
    SQL Server Import and Export Wizard
  6. On the first page of the wizard, click Next
  7. On the second page, click the arrow of the Data Source combo box and select Flat File Source
     
    SQL Server Import and Export Wizard
  8. On the right side of File Name, click the Browse button
  9. Locate and select the Cruise.mdb file
  10. Click Open
  11. Click Next
  12. Click Next
     
    SQL Server Import and Export Wizard
  13. Accept the first radio button and click Next
     
    SQL Server Import and Export Wizard
  14. Make sure the Cabins check box is selected and click Next
  15. Click Next
  16. Click Finish
     
    SQL Server Import and Export Wizard
  17. Click Close

Importing a Spreadsheet

Spreadsheets are probably the easiest files to import in Microsoft SQL Server. This is because a spreadsheet is already created as a table, with the necessary columns and rows. The only real concern is when you are creating the spreadsheet. Although you can put anything in it, you should make sure the Microsoft SQL Server database engine would be able to identify the area where the actual records are (where the records start and where they end).

Practical LearningPractical Learning: Importing a Microsoft Excel Spreadsheet

  1. In the Object Explorer, right-click Cruise1 -> Tasks -> Import Data
  2. On the first page of the wizard, click Next
  3. On the second page, click the arrow of the Data Source combo box and select Microsoft Excel
  4. On the right side of File Name, click the Browse button
  5. Locate and select the Cruise.xlsx file
  6. Click Open
     
    SQL Server Import and Export Wizard
  7. Click Next
  8. Click Next
     
    SQL Server Import and Export Wizard
  9. Accept the first radio button and click Next
  10. In the list, click the check box of 'Employees'
     
    SQL Server Import and Export Wizard
  11. Click Next
  12. Click Next
  13. Click Finish
  14. Click Close

Importing a Text File

One of the types of data you can import into Microsoft SQL Server is a text file. Almost every database environment allows you to import a text file but data from that file must be formatted appropriately. For example, the information stored in the file must define the columns as distinguishable by a character that serves as a separator. This separator can be the single-quote, the double-quote, or any valid character. Data between the quotes is considered as belonging to a distinct field. Besides this information, the database would need to separate information from two different columns. Again, a valid character must be used. Most databases, including Microsoft SQL Server, recognize the comma as such a character. The last piece of information the file must provide is to distinguish each record from another. This is easily taken car of by the end of line of a record. This is also recognized as the carriage return.

These directives can help you manually create a text file that can be imported into Microsoft SQL Server. In practicality, if you want to import data that resides on another database, you can ask that application to create the source of data. Most applications can do that and format the records.

After importing data, you should verify and possibly format it to customize its fields.

To import a text file that contains records:

  1. First create the database that will own the table
  2. In the Object Explorer, right-click the database, position the mouse on Tasks and click Import Data
  3. On the first page of the wizard, click Next
  4. On the second page, click the arrow of the Data Source combo box and select Flat File Source
  5. On the right side of File Name, click the Browse button
  6. Locate and select the text file (such as Employees.txt)
  7. Click Open
  8. On the left side, click Columns
     
    SQL Server Import and Export Wizard
  9. On the left side, click Advanced
  10. As Column 0 is selected, in the right list, click Name and type the desired column name. Click DataType and click the arrow of its combo box. Select the desired data type. If necessary, click OutputColumnWidth and type the desired size
  11. In the middle list, click each column and change its characteristics in the right column. Here are examples:
     
    Name DataType OutputColumnWidth
    EmployeeNumber Unicode string [DT_WSTR] 20
    FirstName Unicode string [DT_WSTR] 20
    LastName Unicode string [DT_WSTR] 20
    HourlySalary decimal [DT_DECIMA]  
  12. To preview the list of columns, under Data Source, click Preview
     
    SQL Server Import and Export Wizard
  13. Click Next 3 times:
     
    Import
  14. Click Next twice
     
    SQL Server Import and Export Wizard
  15. Click Finish
  16. Click Close
 
 
   
 

Previous Copyright © 2000-2013 FunctionX Next