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 every database environment allows you to import 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 for 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

1. Download the Students text file and save it to your hard drive.
2. In the 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:
    
   
7. Click the button on the right side of the File Name edit box
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. Back in the MMC, expand the HighSchool node if necessary, and click Tables. in the right frame, right-click Students and click Design Table.
18. As the first field is selected, type StudentNbr and change its Length to 10
19. Change the other columns as follows:
     
    
20. To save the table, click the Save button on the toolbar:
     
    
21. When a Validation Warnings dialog box presents a few warnings, click Yes
22. Close the table.
23. To view data stored on the table, right-click Students -> Open Table -> Return All Rows