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 meticulously organized. To better manage its information, data of a table is arranged in a series of fields called cells.

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.

 

Practical LearningPractical Learning: Introducing Records

  1. All Computers: Start the computer and log in using your domain account
  2. Server:
    1. Launch Microsoft SQL Server. In the Server Name combo box, make sure the name of the computer is selected. In the Authentication combo box, make sure Windows Authentication is selected. Make sure the account you are using is selected in the User Name combo box. Click Connect
    2. In the Object Explorer, expand Databases
    3. To create a new database, right-click Databases and click New Database...
    4. In the Database Name text box, type BethesdaCarRental3
    5. Click OK
    6. In the Object Explorer, expand Databases and expand BethesdaCarRental3
    7. Under BethesdaCarRental3, expand Security
    8. Right-click Users and click New User...
    9. Create each of the following users and click OK after each
       
      User Name Or For Login Name
      Patricia Student1 DomainName/pkatts
      Gertrude Student2 DomainName/gmonay
      Raymond Student3 DomainName/rmonay
      Orlando Student4 DomainName/operez
  3. Each Student:
    1. Start Microsoft SQL Server
    2. In the Server Name combo box, select the name of the server or type it
    3. In the Authentication combo box, select SQL Server Authentication
    4. In the User Name combo box, type the name of the domain, followed by \, and followed by the login name you were given
    5. In the Password combo box, type your password
    6. Click Connect
  4. All Computers: In the Object Explorer, expand the BethesdaCarRental3
  5. All Computers: In the Object Explorer, right-click BethesdaCarRental3 and click New Query
  6. All computers: Type:
    USE BethesdaCarRental3;
    GO
  7. All computers: To execute, press F5
  8. All computers: Click inside the top section of the Query window and press Ctrl + A to select everything
  9. All Computers: Press Delete
  10. All Computers: In the Object Explorer, under BethesdaCarRental3, right-click Tables and click New Table...
  11. Server: Create a table with the following columns and characteristics
     
    Column Name Data Type
    Category Code nchar(4)
    Category nvarchar(20)
  12. pkatts (Student1): Create a table with the following columns and characteristics
     
    Column Name Data Type
    Employee Number nchar(6)
    Date Hired date
    First Name nvarchar(20)
    Last Name nvarchar(20)
  13. gmonay (Student2): Create a table with the following columns and characteristics
     
    Column Name Data Type
    Driver's License Number nvarchar(20)
    Customer Name nvarchar(50)
    Date Issued date
    State Issued nchar(2)
  14. rkouma (Student3): Create a table with the following columns and characteristics
     
    Column Name Data Type
    Tag Number nvarchar(12)
    Make nvarchar(40)
    Model nvarchar(40)
    Car Year int
  15. operez (Student3): Create a table with the following columns and characteristics
     
    Column Name Data Type
    Order Number int
    Processed By nchar(6)
    Processed For nvarchar(20)
    Car Rented nvarchar(12)
    Start Date datetime
    End Date datetime
    Rate Applied money
  16. All Computers: Close the table
  17. All Computers: When asked whether you want to save, click Yes
  18. Server: Type Categories as the name of the tavble and press Enter
  19. pkatts (Student1): Type Employees and click OK
  20. gmonay (Student2): Type Customers and press Enter
  21. rkouma (Student3): Type Cars and click OK
  22. operez (Student4): Type OrderProcessing and press Enter
  23. All Computers: In the Object Explorer, under BethesdaCarRental3, expand Tables

Table Data Navigation in the SQL Server Management Studio

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

Tables and Permissions

 

Introduction

As far as users are concerned, the primary reason for using a database is to open a table and use its records. You on the other hand need to control who has access to a table and what a particular user can do on it. Fortunately, Microsoft SQL Server can let you control all types of access to the records of any table of your database. As seen for databases, you can grant or deny access to a table, to some users individually or to a group of users. Of course, you can work visually or programmatically.

Before exercising security on a table for a user, you must have created a user account for that user.

Managing Permissions on a Table

To visually grant or deny operations at the table level, in the Object Explorer, right-click the table and click Properties. In the Select a Page list, click Permissions. In the Users or Roles list, click the name of the user or click Select to locate the user. In the Permissions column, locate the type of permission you want. Manage the operations in the Grant and in the Deny columns.

In Microsoft SQL Server, every operation of a table has its own permissions. The list of permissions can be seen in the lower section of the Table properties if you click Permissions:

Table Properties

More than on a database, the permissions of a table are very interconnected. This means that giving one type of access may not be enough to achieve the right result. This also means that you must know how to combine permissions:

  • ALTER: If this permission is granted, a user can open a table in design view and change its structure (design). For example, a person can add a new column or delete an existing column. In most cases, regular users should be denied this right
  • INSERT: This permission allows a user to create new records. If you don't want a user to add new records to the table, deny this permission
  • UPDATE: This is one of the most important permissions when it comes to data entry
  • DELETE: This permission allows a user to delete one or more records. The reverse is to deny this right so the user can only see the records but cannot remove them

The basic formula to programmatically grant one or more permissions to a user is:

GRANT Permission1,Permission2, Permission_n
ON [ OBJECT :: ][ schema_name ].object_name [ (Column1, Column2, Column_n ] ) ]
TO Login1, Login2, Login_n ]

The basic formula to programmatically deny (a) permission(s) is:

DENY Permission1,Permission2, Permission_n
ON [ OBJECT :: ][ schema_name ].object_name [ (Column1, Column2, Column_n ] ) ]
TO Login1, Login2, Login_n ]

You start with the GRANT (or DENY) keyword. To grant a permission, type it. After specifying the types of permissions you want, type ON or ON OBJECT::. This is followed by the name of the object, like a table, on which you want to grant permissions. If necessary, or this is optional, precede the name of the object with the name of the schema. After the name of the object, type TO, followed by the login name that will receive the permission. Here is an example:

USE master;
GO
CREATE DATABASE Exercise1;
GO
USE Exercise1;
GO
CREATE TABLE Employees
(
    EmployeeNumber nchar(10),
    FirstName nvarchar(20),
    LastName nvarchar(20),
);
GO
CREATE USER [Peter Mukoko]
FOR LOGIN rkouma;
GO

GRANT ALTER 
ON OBJECT::Employees
TO [Peter Mukoko];
GO

If you want to combine permissions, separate them with commas. Here is an example:

USE Exercise1
GRANT INSERT, UPDATE
ON OBJECT::dbo.Payroll
TO [James Galvin];
GO

If you want to grant the permission(s) to more than one account, separate them with commas.

Extending Permissions

As mentioned for a database, you can give one account the ability to grant or deny permissions to other accounts. To do this visually, access the Database Properties for the database. In the Users or Roles section, select the user. In the Persmissions section, use the check boxes in the With Grant column.

The formula to programmatically give an account the ability to grant or deny permissions to other accounts is:

GRANT Permission1,Permission2, Permission_n
TO Login1, Login2, Login_n
WITH GRANT OPTION

In this formula, you add the WITH GRANT OPTION expression.

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

Data Entry and Permissions

For a user to be able to create records on a table, his or her account must be granted the INSERT permission. Of course, the user must be allowed to open the 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:

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

Practical LearningPractical Learning: Introducing Data Entry

  1. Server: In the Object Explorer, under the Tables node of BethesdaCarRental3, right-click Categories and click Edit Top 200 Rows
  2. pkatts (Student1): In the Object Explorer, under the Tables node of BethesdaCarRental3, right-click Employees and click Edit Top 200 Rows
  3. gmonay (Student2): In the Object Explorer, under the Tables node of BethesdaCarRental3, right-click Customers and click Edit Top 200 Rows
  4. rkouma (Student3): In the Object Explorer, under the Tables node of BethesdaCarRental3, right-click Cars and click Edit Top 200 Rows
  5. operez (Student4): In the Object Explorer, under the Tables node of BethesdaCarRental3, right-click OrderProcessing and click Edit Top 200 Rows
  6. Click the first empty cell under Country Name, type Cote d'Ivoire and press Tab
  7. Type 322460 for the area and press Tab
  8. Type 21,058,798 and press Enter
  9. Notice that you receive an error because the commas are not allowed:
     
  10. Click OK on the error message box.
  11. Change the value to 21058798 People and press Tab
  12. Notice that you receive another error because the column is configured for a natural number and not a string
  13. Click OK on the error message box and delete People
  14. Click under Internet Code, type ci
  15. Click the field under Capital, type Yamoussoukro and press Tab twice
  16. Complete the table as follows:
     
    Country Name Area Population Capital Internet Code
    Cote d'Ivoire 322460 21058798 Yamoussoukro ci
    Panama 78200 34106766 Panama pa
    Australia 7686850 21515754 Canberra au
    Canada 9984670 33759742 Ottawa ca
    Iran 1648000 76923300 Tehran ir
  17. Close the table

Programmatic Data Entry

 

Introduction

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

Author Note

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.

 

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.

Practical LearningPractical Learning: Performing Adjacent Data Entry

  1. Click inside the Query window and press Ctrl + A to select everything
  2. To create one record, type the following:
    USE WorldStatistics1;
    GO
    INSERT INTO Countries
    VALUES(N'Angola', 1246700, 13068161, N'Luanda', N'ao');
    GO
  3. Press F5 to execute

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.

Practical LearningPractical Learning: Performing Random Data Entry

  1. To perform data entry, edit the statement as follows:
    USE WorldStatistics1;
    GO
    INSERT Countries([Country Name],
    		 Capital,
    		 [Internet Code],
    		 [Country Population],
    		 Area)
    VALUES(N'China', N'Beijing', N'cn', 1313973713, 9596960);
    GO
  2. Press F5 to execute the statement
  3. To perform other entries, edit the statement as follows:
    USE WorldStatistics1;
    GO
    INSERT Countries(Capital, [Internet Code], [Country Name])
    VALUES(N'Nouakchott', N'mr', N'Mauritania')
    GO
    INSERT Countries([Internet Code], [Country Population], [Country Name])
    VALUES(N'ro', 22303552, N'Romania')
    GO
    INSERT Countries(Area, [Country Name], [Country 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

Creating Multiple Records

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:

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. Here is an example:

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

Practical LearningPractical Learning: Creating Multiple Records

  1. Click inside the Query window and press Ctrl + A
  2. To enter various records at the same time, type the following statement:
    USE WorldStatistics1;
    GO
    INSERT INTO Countries
    VALUES(N'Mexico', 1972550, 112468855, N'Mexico City', N'mx'),
          (N'South Africa', 1219912, 49109107, N'Pretoria', N'za'),
          (N'Iraq', 438317, 29671605, N'Baghdad', N'iq'),
          (N'United States', 9826630, 310232863, N'Washington, DC', N'us'),
          (N'Saudi Arabia', 2149690, 25731776, N'Riyadh', N'sa');
    GO
  3. Press F5 to execute the statement

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 keyword. Here is an example:

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

Practical LearningPractical Learning: Creating Multiple Records

  1. Click inside the Query window and press Ctrl + A
  2. To enter various records at the same time, enter the following statement:
    USE WorldStatistics1;
    GO
    INSERT INTO Countries
    VALUES(N'Mexico', 1972550, 112468855, N'Mexico City', N'mx'),
          (N'South Africa', 1219912, 49109107, N'Pretoria', N'za'),
          (N'Iraq', 438317, 29671605, N'Baghdad', N'iq'),
          (N'United States', 9826630, 310232863, N'Washington, DC', N'us'),
          (N'Saudi Arabia', 2149690, 25731776, N'Riyadh', N'sa');
    GO
  3. Press F5 to execute the statement
  4. Click inside the Query window and press Ctrl + A to select everything
  5. To delete the database, type the following:
    USE master;
    GO
    DROP DATABASE WorldStatistics1;
    GO
  6. To execute, press F5
 
 
 

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 © 2003-2011 FunctionX.com Next