Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Data import and export » Data import

 

Importing data with the Import Wizard

Use the Interactive SQL Import Wizard to select a source, format, and destination table for the data. You can import data from text files, fix format files, and shapefiles, into an existing table or a new table.

Prerequisites

If you import data into an existing table, you must be the owner of the table, have SELECT and INSERT privileges on the table, or have the SELECT ANY TABLE and INSERT ANY TABLE system privileges.

If you import data into a new table, you must have the CREATE TABLE, CREATE ANY TABLE, or CREATE ANY OBJECT system privilege.

Context and remarks

You can also use the Import Wizard to import data between:

  • databases of different types, such as between a SQL Anywhere database and an UltraLite database.

  • databases of different versions (as long as you have an ODBC driver for each database), such as between a SQL Anywhere 16 database and a SQL Anywhere 11 database.

Use the Interactive SQL Import Wizard when you:

  • want to create a table at the same time you import the data

  • prefer using a point-and click interface to import data in a format other than text

 Task
  1. In Interactive SQL, click Data » Import.

  2. Follow the instructions in the Import Wizard.

Results

The data is imported into the specified database.

Example

Perform the following steps to import data from a file into the SQL Anywhere sample database:

  1. Create and save a text file named newProducts.csv with the following values:

    ID,Name,Description,Size,Color,Quantity,UnitPrice
    701,Shorts,Spandex Cycling,Small,Black,500,23.00
    702,Shorts,Spandex Cycling,Medium,Black,500,23.00
    703,Shorts,Spandex Cycling,Large,Black,200,25.00
  2. In Interactive SQL, click Data » Import.

  3. Click In a text file, and then click Next.

  4. Click Browse and navigate to the folder containing newProducts.csv.

    The file is located relative to the client computer, not the database server computer.

  5. Click newProducts.csv, then click Open.

  6. Click In an existing table.

  7. Click Products, then click Next.

  8. In the Field separator list, click Comma( , ).

  9. In the Other options list, click The first line contains column names.

  10. Click Next.

  11. Click Import.

  12. Click Close.

    The SQL statements created by the wizard are stored in the history list when the import finishes.

    To view the generated SQL INPUT statement, in the SQL menu, click Previous SQL.

    The INPUT statement generated by the Import Wizard appears in the SQL Statements pane:

    --  Generated by the Import Wizard
    input into "GROUPO"."Products" from 'C:\\LocalTemp\\newSwimwear.csv'
        format text escapes on escape character '\\'
        delimited by ',' encoding 'Cp1252' skip 1

Perform the following steps to import data from the SQL Anywhere sample database into an UltraLite database:

  1. Connect to an UltraLite database, such as, C:\Users\Public\Documents\SQL Anywhere 16\Samples\UltraLite\CustDB\custdb.udb.

  2. In Interactive SQL, click Data » Import.

  3. Click In a database. Click Next.

  4. In the Database type list, click SQL Anywhere.

  5. In the Action dropdown list, click Connect with an ODBC Data Source.

  6. Click ODBC Data Source name, and then in the box below type SQL Anywhere 16 Demo.

  7. Click Next.

  8. In the Table name list, click Customers. Click Next.

  9. Click In a new table.

  10. In the Table name field, type SQLAnyCustomers.

  11. Click Import.

  12. Click Close.

  13. To view the generated SQL statement, click SQL » Previous SQL.

    The INPUT statement generated by the Import Wizard appears in the SQL Statements pane.

    --  Generated by the Import Wizard
    input using 'dsn=SQL Anywhere 16 Demo;CON=''''' 
        from "GROUPO.Customers" into "SQLAnyCustomers" 
        create table on