Lesson 3: Importing Data in SQL

You can import data into SQLScope from another datasource by using the Insert command. Before completing the following exercises, you must import data into the tables you created. The example given in this section will step you through the process.


Syntax:
Insert into tablename
(fieldname, fieldname, fieldname)
Values
(@fieldname, @fieldname, @fieldname);

Explanation:

• Before you can import, you must create a data file in one of the following formats:
  • Btreive unformatted (.unf) -- File layout.
    • Logical, date, and time fields are in binary format.
    • Each row is preceded by its length, followed by a comma.
    • Each row is terminated with a carriage return.
  • Standard Data Format (.sdf) -- File layout.
    • All data is represented in ASCII characters.
    • A comma is used to separate fields.
    • Character fields are enclosed in double quotes.
    • Each row is terminated with a carriage return.
  • ASCII
    • Each row is preceded by a comma or a space.
    • Each field and each row is a specified length.
    • Each row is terminated with a carriage return.
  • DIF (.dif)
    • Each file is made up of two sections: header and data.
    • Each row has a standard length.
    • Each field has a standard length.
• The Values clause is used to specify substitution variables.
• Substitution variables must be preceded by an @ sign.
• Each field in the Insert statement must be in the same order as the corresponding field in the data file.

To import data:
Choose Import from the File menu to open the Import dialog box.

1. In the File Type box, specify the type of file you want to import. 
2. In the Drive box, specify the appropriate drive. 
3. In the Directories box, specify the appropriate directory.
4. In the File Name box, specify the name of the file from which to import.
5. If you would like the process to run in the background while you continue working, click on the Backgroundfield.
6. Click on Import to begin the import process.

Example:Import data into the TrnVendor and TrnAPDoc files.

1. Download the files:

Click here to download a zip.file

2. Type the following in the SQL text box:

Insert into TrnVendor
(Vendid, Name, Add1, City, State, CurrBal, ExpAcct )
Values
(@Vendid, @Name, @Add1, @City, @State, @CurrBal, @ExpAcct)

3. Click on File on the Menu bar.
4. Click on Import on the drop-down menu to open the Import dialog box.
5. In the Drive box, specify the drive to which you downloaded the files in step #1.
6. In the Directories box, specify the directory to which you downloaded the files in step #1.
7. In the File Name field, type imvendor.sdf.
8. Click on Import.
9. You should receive the following message:

Importing has completed.
Records: 30

10. Type the following in the SQL Text box:

Insert into TrnAPDoc
(Vendid, RefNbr, DocDate, OrigDocAmt, CuryDocBal, OpenDoc) 
Values
(@Vendid, @RefNbr, @DocDate, @OrigDocAmt, @CuryDocBal, @OpenDoc)

11. Click on File on the Menu bar.
12. Click on Import on the drop-down menu to open the Import dialog box.
13. In the Drive box, specify the drive to which you downloaded the files in step #1.
14. In the Directories box, specify the directory to which you downloaded the files in step #1.
15. In the File Name field, type imapdoc.sdf.
16. Click on Import.
17. You should receive the following message:

Importing has completed.
Records: 50