The SQLLOAD utility loads user data from a formatted file into an SQL database. Typically, the source file for the load is created by executing the SQLDUMP utility. The SQLLOAD utility can process a source file created by another application or utility, if the format of the file conforms to SQLLOAD requirements. The file extension made available to SQLLOAD for processing must be .dsql. See the entry on SQLDUMP for a description of the required file format.

To load data onto the multi-tenant tables from a formatted file, the new command-line option (-n) on the utility allows you to specify the list of tenants for which table data must be loaded. You can also use the SQLLOAD utility to load data of multi-tenant groups to the existing multi-tenant database.

Before you can execute SQLLOAD against a database server, the server must be configured to accept SQL connections and must be running.

You can also use the SQLLOAD utility to load data onto partitioned tables. For more information, see the Load partitioned tables.

Use the following syntax for the SQLLOAD utility:

Syntax 

sqlload -u user_name[@domain_name ]
  [-a password ][ -C code-page-name ] 
    -t [owner_name]table_name1  
  [[,owner_name]table_name2, ...] 
    [ -l log_file_name]   
    [ -b badfile_name]  
    [ -e max_errors]   
    [ -s skipcount ]  
    [ -m max_rows]   
    [ -F comma | quote ] 
    [ -n tenant_name1,...]      
          [ -g group_name1,...]         
          [ -p partition_name1,...]    database_name

The following example demonstrates how to establish a connection using the SQLLOAD utility with a DBA username and its associated password: 

sqlload -t PUB.customer -u <dba_user> -a <dba_password> 
progress:T:<hostname>:<database_port>:<database_name>
Note: When connecting with a blank password, you can specify the password as either '' (two single quotes) or \'\'. Both represent an empty string and are interpreted as a valid blank password by the SQL utility.

The SQLLOAD utility accepts all encoding prefixes supported by the genpassword utility for encoding passwords before transmitting them over the network to the OpenEdge SQL server for authentication. If the password is provided in plain text, it is encoded using ae2h4 before being transmitted over the network.

For more information, see genpassword and Encoding prefix.

For example, if the password for connecting to the database using the SQLLOAD utility is progress, you can generate the encoded credentials with the ae2h1 prefix as follows: 
genpassword -password progress -prefix ae2h1
As a result, the genpassword utility generates an ae2h1 encoded password:
ae2h1::e41027ea0710535e4bd4e2987d5647c95c1dd9988a7b5a4c18ec208017e7b67a9f4546afa452ff0d332f1fa703101923
You can now connect to the database using the SQLLOAD utility by running the following command:
sqlload -t PUB.customer -u user1  
-a ae2h1::e41027ea0710535e4bd4e2987d5647c95c1dd9988a7b5a4c18ec208017e7b67a9f4546afa452ff0d332f1fa703101923 
progress:T:localhost:9889:test

The SQLLOAD utility reads application data from variable‑length text‑formatted files and writes the data into the specified database. The column order is identical to the table column order. SQLLOAD reads format and content header records from the dump file. You can load multiple tables in a single execution by specifying multiple table names, separated by commas. Data for one table is from a single dump file. Every source file corresponds to one database table. For example, if you specify 200 tables in the SQLLOAD command, you will load 200 database tables.

The format for the records in the input files is similar to the ABL .d file dump format. The maximum record length SQLLOAD can process is 32K.

Each database record read is locked in Exclusive mode to maintain consistency. You must ensure that the SQL Server has a lock table large enough to contain one lock for every record in the table. The default lock table size is 8192 locks.

SQLLOAD writes any errors to standard output and halts the loading process for any error so that data integrity is not compromised.

Example: SQLLOAD of two dump files

The following example directs the SQLLOAD utility to load the data from two dump files into the salesdb database. The input files to SQLLOAD must be tucker.customers.dsql and tucker.products.dsql. 

sqlload -u tucker -a sulky -t tucker.customers,tucker.products
progress:T:thunder:4077:salesdb

Example: SQLLOAD from appropriately named files

The following example directs SQLLOAD to load the data from all appropriately named dump files into the specified tables in the salesdb database.

sqlload -u tucker -a sulky -t %.cust%,%.invent%,%.sales%
progress:T:thunder:4077:salesdb

The database_name must be the last parameter given.

The character set used by SQLLOAD must match the character set information recorded in each dump file. If the character sets do not match, the load is rejected. You can use the SQL_CLIENT_CHARSET environment variable to specify a character set.

Each dump file you create with SQLDUMP contains character set information about that file. The character set recorded in the dump file is the client character set. The default character set for all non‑JDBC clients is taken from the local operating system through the operating system APIs. JDBC clients use the Unicode UTF-8 character set. To use a character set different than that used by the operating system, set the SQL_CLIENT_CHARSET environment variable to the name of the preferred character set. You can define any ABL-supported character set name. The name is not case-sensitive.

At run time, SQLLOAD reports an error if it detects a mismatch between the code page of the dump file being loaded and the code page of the client running SQLLOAD.

By default, SQLLOAD displays promsgs messages using the code page corresponding to code-page-name. That is, if you are restoring a French database and code-page-name specifies the name of a French code page, the client displays promsgs messages using the French code page (unless you specify a different code page by setting the client's SQL_CLIENT_CHARSET_PROMSGS environment variable).

SQLLOAD does not support the following characters in schema names:

  • Double quote (")
  • Forward slash (/)
  • Backslash (\)

SQLLOAD, however, does support schema names that contain special characters, such as a blank space, a hyphen (-), or a pound sign (#). These names must be used as delimited identifiers. Therefore, when specifying names with special characters on a UNIX command line, follow these rules:

  • Use double quotes to delimit identifiers.
  • So that the command line does not strip the quotes, use a backslash (\) to escape the double quotes used for delimited identifiers.
  • Use double quotes to enclose any names with embedded spaces, commas, or characters special to a command shell (such as the Bourne shell). This use of quotes is in addition to quoting delimited identifiers.

Example: SQLLOAD of files with delimited identifiers

To load the table Yearly Profits, use the UNIX command‑line syntax, as shown in the following example.

sqlload -u xxx -a yyy -t "\"Yearly Profits\"" progress:T:<hostname>:<database_port>:<database_name>

In Windows NT, the command interpreter rules for the use of double quotation marks varies from UNIX.