|
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.
|
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
Learning: Importing Data From an External Source
|
|
- Download the Students text file and save
it to your hard drive
- In the SQL Server Enterprise Manager, right-click the Databases node and click
New Database...
- Type ROSH and press Enter
- In the left frame, right-click ROSH, position the mouse on All
Tasks and click Import Data
- On the first page of the wizard, click Next
- On the second page, click the arrow of the Data Source combo box and
select Text File:
- Click the button on the right side of the File Name edit box
- Locate the folder where you saved the Students text file. Select the
file and press Enter:
- Click Next
- 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
- On the fourth page, accept all defaults and click Next.
- On the fifth page, make sure that the Destination is SQL Server and
the destination Database is HighSchool. Then click Next
- Accept all defaults from the sixth and the seventh pages. Then click
Next.
- On the eighth page, click Finish
- When you receive a confirmation of "Successfully Copied, click
OK
- On the Executing Package page, click Close
- 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
- In the Server Explorer, right-click the name of the server and click
Refresh.
Expand the Tables node under the ROSH database
- Right-click Students and click Design Table
- As the first field is selected, type StudentNbr and change its
Length to 10
- Change the other columns as follows:
- To save the table, click the Save button on the toolbar:
- When a Validation Warnings dialog box presents a few warnings, click
Yes
- Close the table
- To view data stored on the table, in the Server Explorer,
double-click Students
- Close the table
|
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
Learning: Setting Default Values
|
|
- In the design view of the table and in the top section, click Gender
- In the lower section, click Default Value and type 'Unknown'
- In the top section, click State
- In the lower section, click Default Value and type 'MD'
- Save the table
|
|
