Introduction to Records
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.
 |
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 Learning: Introducing Data Entry
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
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:
|
 |
Practical Learning: Introducing Data Navigation
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.
|
|
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:
|
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:
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 Learning: Creating a Record
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:
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
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 |
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.
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:
As you can see, the default lengh is set to 10. If you want to change:
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
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: |
 |
CREATE TABLE Presidents
(
FirstName nvarchar(24),
LastName nvarchar(24)
);
GO
INSERT INTO Presidents VALUES(N'Julius', N'Nyerere');
GO
|
|
ALTER TABLE Academics.StudentsGradeScale ALTER COLUMN LetterGrade nvarchar(5); GO
ALTER TABLE Academics.StudentsGradeScale ADD Descriptor nvarchar(20); GO
| 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 |
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.
|
|
| 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 |
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.
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
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
|
|
| 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 |
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.
|
|
| Commission Rate |
| 1.4 |
| .54 |
| .28 |
| .1875 |
| .09 |
| .0075 |
|
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.
|
|
| 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 |
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 |
 |
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'.
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
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.
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 |
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.
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:
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.
New-Item 'C:\Red Oak High School' -type directory
|
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.
|
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).
|
|
|
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:
| 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] |
|
|
||
| Previous | Copyright © 2000-2022, FunctionX | Next |
|
|
||