Home

Assistance With Data Entry

Introduction

Microsoft SQL Server and the SQL provide various ways to assist you with data entry. For example, if you have a table in a Microsoft SQL Server database, a Microsoft Access database, or another system, such as a text file, you can import the values of that table. Another type of assistance you can get with data entry is to copy records from one table to another.

Data Import

Another technique used to perform data entry 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 importing data into Microsoft SQL Server.

The easiest type of data that can be imported into SQL Server, and which is available on almost all database environments is the text file. Almost any database application you can think of can be imported as a text file but data from that file must be formatted in an acceptable format. 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. SQL Server is able to recognize the double-quote as a valid separator of columns. 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 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 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 it so another application can easily use such data. That is the case for the data we will use in the next exercise: it is data that resided on a Microsoft Access database and was prepared to be imported in SQL Server.

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

Practical LearningPractical Learning: Importing Data From an External Source
  1. Download the Students text file and save it to your hard drive
  2. In the SQL Server Enterprise Manager, right-click the Databases node and click New Database...
  3. Type ROSH and press Enter
  4. In the left frame, right-click ROSH, position the mouse on All Tasks and click Import Data
  5. On the first page of the wizard, click Next
  6. On the second page, click the arrow of the Data Source combo box and select Text File:
     
    DTS Import/Export Wizard
  7. Click the button on the right side of the File Name edit box Browse
  8. Locate the folder where you saved the Students text file. Select the file and press Enter:
     
  9. Click Next
     
  10. On the third page, make sure the file is type ANSI and the Row Delimiter is the Carriage Return-Line Feed ({CR}{LF}) and accept all other defaults. Click Next
     
  11. On the fourth page, accept all defaults and click Next.
  12. On the fifth page, make sure that the Destination is SQL Server and the destination Database is HighSchool. Then click Next
  13. Accept all defaults from the sixth and the seventh pages. Then click Next.
  14. On the eighth page, click Finish
     
  15. When you receive a confirmation of "Successfully Copied, click OK
  16. On the Executing Package page, click Close
  17. Position the mouse on Server Explorer to display it. In the Server Explorer, expand the server, followed by the SQL Servers node, followed by the name of the server
  18. In the Server Explorer, right-click the name of the server and click Refresh.
    Expand the Tables node under the ROSH database
  19. Right-click Students and click Design Table
     
  20. As the first field is selected, type StudentNbr and change its Length to 10
  21. Change the other columns as follows:
     
  22. To save the table, click the Save button on the toolbar:
     
  23. When a Validation Warnings dialog box presents a few warnings, click Yes
  24. Close the table
  25. To view data stored on the table, in the Server Explorer, double-click Students
  26. Close the table
 

The Default Value

When performing data entry, the records under a certain column usually have the same value. For example, for a local database with a table that includes an address, most employees would live in the same state and the same country. When creating a column with a value that occurs regularly, you can specify that value as default.

To specify the default value in the SQL Server Enterprise Manager or the Server Explorer, display the table is design view. To proceed, first click the column in the top section of the table. Then, in the lower section, click Default Value and type the desired value in single-quotes. Here is an example:

To specify the default value in a SQL statement, when creating the column, before the semi-colon or the closing parenthesis of the last column, assign the desired value to the DEFAULT keyword. Here are examples:

-- =============================================

-- Database: Sydney University

-- Table:    StaffMembers

-- =============================================

IF EXISTS(SELECT name 

	  FROM 	 sysobjects 

	  WHERE  name = N'StaffMembers' 

	  AND 	 type = 'U')

    DROP TABLE StaffMembers

GO



CREATE TABLE StaffMembers (

FullName VARCHAR(50),

Address VARCHAR(80),

City VARCHAR(40),

State VARCHAR(40) DEFAULT = 'NSW',

PostalCode VARCHAR(4) DEFAULT = '2000',

Country VARCHAR(20) DEFAULT = 'Australia')

GO

If you are creating the table in a Windows Forms Application, use the same rules of the SQL statement and create the table as we have done already.

After creating the table, the user doesn't have to provide a value for a column that has a default. If the user doesn't provide the value, the default would be used when the record is saved.

If the user provides a value for a column that has a default value and then deletes the value, the default value rule would not apply anymore: The field would simply become empty
 
If you create a Windows Forms Application and provide a form that allows the user to perform data entry for a table using a form, the default values for columns would not display in the Windows control. When writing your code, you can omit passing the values for the columns that have default values. In this case, the SQL interpreter, not the C++ compiler would use the default values for the columns you omit.
 

Practical LearningPractical Learning: Setting Default Values
  1. In the design view of the table and in the top section, click Gender
  2. In the lower section, click Default Value and type 'Unknown'
  3. In the top section, click State
  4. In the lower section, click Default Value and type 'MD'
  5. Save the table
 
Previous Copyright © 2005-2016, FunctionX Next