Skip to main content

LOAD DATA (SQL)

Loads data into a table.

Synopsis

Load from File

LOAD DATA FROM FILE filePath INTO table LOAD DATA FROM FILE filePath INTO table (column, column2, ...) LOAD DATA FROM FILE filePath COLUMNS (header type, header2 type2, ...) INTO table ... LOAD DATA FROM FILE filePath COLUMNS (header type, header2 type2, ...) INTO table ... VALUES (header, header2, ...)  LOAD DATA FROM FILE filePath INTO table ... USING jsonOptions
Load from JDBC Source

LOAD DATA FROM JDBC CONNECTION jdbcConnection TABLE jdbcTable INTO table LOAD DATA FROM JDBC CONNECTION jdbcConnection TABLE jdbcTable INTO table (column,column2, ...) LOAD DATA FROM JDBC CONNECTION jdbcConnection TABLE jdbcTable INTO table ... VALUES (header,header2 ...) 

Description

The LOAD DATA command loads data from a source into an previously defined InterSystems IRIS® SQL table. The source can be a data file or a table accessed using JDBC. Use this command for the rapid population of a table with well-validated data.

If the table you are loading data into is empty, LOAD DATA populates the table with the source data rows. If the table already contains data, LOAD DATA inserts the source data rows into the table without overwriting any existing rows.

When you load data, the %ROWCOUNTOpens in a new tab variable indicates the number of rows successfully loaded. If a row in the input data contains an error, LOAD DATA skips loading this rows and proceed with loading the next row. SQLCODE does not report this as an error, but the %SQL_Diag.Result log indicates how many records failed to load. For more details, see View Diagnostic Logs of Loaded Data.

Note:

The LOAD DATA command uses an underlying Java-based engine that requires a Java Virtual Machine (JVM) installation on your server. If you already have a JVM set up and accessible in your PATH environment variable, then the first time you use LOAD DATA, InterSystems IRIS automatically uses that JVM to start an External Language Server. To customize your External Language Server to use a specific JVM, or to use a remote server, see Managing External Server Connections.

Load from File

Load from File Without Headers

Use these syntaxes if your source file does not contain a header row in the first line of the file. Otherwise, LOAD DATA loads the header row into the table.

  • LOAD DATA FROM FILE filePath INTO table loads the source data from the file specified by filePath into the target SQL table. By default, LOAD DATA matches the columns from the data source to the target table by position. LOAD DATA uses the SQL column order (SELECT * column order).

    • If the data source has more columns than the input table, then the excess columns are ignored and not loaded into the table.

    • If the data source has fewer columns the input table, none of the data is loaded into the table.

    The LOAD DATA command expects that the data type of the loaded data matches the data type of the target table columns.

    This statement loads all the columns from the countries CSV source file into the columns that are in the corresponding positions of the target Sample.Countries table.

    LOAD DATA FROM FILE 'C://mydata/countries.csv'
    INTO Sample.Countries
  • LOAD DATA FROM FILE filePath ... INTO table (column, column2, ...) loads source data positionally for only the specified target table columns. If the data source has fewer columns than the input table, then those columns are empty in the inserted rows.

    This statement loads the first three columns from the countries CSV file into the Name, Continent, and Region columns of the Sample.Countries table. Even if the table stores these columns in a different order, or if there are columns in between the three shown here, LOAD DATA still loads data only into Name, Continent, and Region.

    LOAD DATA FROM FILE 'C://mydata/countries.csv'
    INTO Sample.Countries (Name,Continent,Region)
  • LOAD DATA FROM FILE filePath COLUMNS (header type, header2 type2, ...) INTO table enables you to load data from source files that have a different column order than the target table. The COLUMNS clause provides header names and data types for the columns in the source files. The header names must match the names of columns in the target table and the data type must be consistent with the data types of those table columns.

    • If the INTO table clause specifies target columns, then the columns named in the COLUMNS clause must also appear in the INTO table clause, but they can be in any order.

    • If the INTO table clause does not specify target columns, then LOAD DATA loads the source columns positionally into the table. The COLUMNS clause must name all columns that appear in the target table.

    This statement loads three columns from the countries CSV file into corresponding columns in the Sample.Countries table. If the Sample.Countries table has a different column order than the source file (for example, Name, SurfaceArea, Continent instead of Name, Continent, SurfaceArea), the table column order does not change.

    LOAD DATA FROM FILE 'C://mydata/countries.csv'
    COLUMNS (
        Name VARCHAR(50),
        Continent VARCHAR(30),
        SurfaceArea Integer)
    INTO Sample.Countries (Name,Continent,SurfaceArea)
  • LOAD DATA FROM FILE filePath COLUMNS (header type, header2 type2, ...) INTO table ... VALUES (header, header2, ...) additionally enables you to load a subset of columns from the source file into the target columns. These column names do not need to match the target table column names.

    The VALUES clause specifies the source columns, as named by the headers in the COLUMNS clause, to load into the target table.

    • If the INTO table clause specifies target columns, then LOAD DATA loads the source columns into the target columns in the order those columns are specified. The number of source column headers in VALUES must match the number of columns in the INTO table clause.

    • If the INTO table clause does not specify target columns, then LOAD DATA loads the source columns positionally into the table. The number of source headers in VALUES must match the number of columns in the table.

    This statement loads three columns from the countries CSV file into corresponding columns in the Sample.Countries table. The COLUMNS clause includes a header name for an additional column, src_continent, that is not loaded into the table. This column name is ignored, but it must be included so that LOAD DATA can load the data from the subsequent columns (src_region and src_surface_area) into the table.

    LOAD DATA FROM FILE 'C://mydata/countries.csv'
    COLUMNS (
        src_name VARCHAR(50),
        src_continent VARCHAR(30),
        src_region VARCHAR(30),
        src_surface_area INTEGER)
    INTO Sample.Countries (Name,SurfaceArea,Region)
    VALUES (src_name,src_surface_area,src_region)

    If you specify the VALUES clause without the COLUMNS clause, then the VALUES clause is ignored.

Load from File with Headers and Specify Options

Use this syntax if the first line of your source file contains a header row. Using this syntax, you can specify an option to skip the header row. Other options include changing the default column or line separator, skipping additional rows beyond the header, and changing the default escape character.

  • LOAD DATA FROM FILE filePath INTO table ... USING jsonOptions specifies loading options by using a JSON object or a string containing a JSON object.

    This statement uses a JSON object to specify that the file contains a header row, so that LOAD DATA does not include this row in the table. In this statement, it is assumed that the header names in the countries CSV file match the header names of the Sample.Countries table columns.

    LOAD DATA FROM FILE 'C://mydata/countries.csv'
    INTO Sample.Countries
    USING {"from":{"file":{"header":true}}}
    Note:

    If the header text does not validate against the field data type, such as an integer field with a header named "Total", LOAD DATA might omit the header row anyway. However, this method of validation rejection is unreliable and is not recommend. Omit the header with a USING clause instead.

    The statement loads data from three columns in the countries CSV file into the three corresponding columns in the Sample.Countries table. In this statement, the header names in the countries CSV file do not match the header names of the Sample.Countries table columns. The VALUES clause specifies the column header names obtained from the file. Data from these columns is then loaded into the table columns that are in the corresponding position of the INTO table clause.

    LOAD DATA FROM FILE 'C://mydata/countries.csv'
    INTO Sample.Countries (Name,Region,SurfaceArea)
    VALUES (country_name,region_name,surface_area)
    USING {"from":{"file":{"header":true}}}

Load from JDBC Source

  • LOAD DATA FROM JDBC CONNECTION connection TABLE jdbcTable INTO table loads data from an external JDBC data source into the target table. The data source, jdbcTable, is a JDBC-compliant SQL table that you connect to by using a defined SQL Gateway Connection, connection. For more details, see Connecting the SQL Gateway via JDBC.

    This statement loads all columns from the JDBC source table, countries, into the corresponding columns of the Sample.Countries table.

    LOAD DATA FROM JDBC CONNECTION MyJDBCConnection
    TABLE countries
    INTO Sample.Countries
  • LOAD DATA FROM JDBC CONNECTION connection TABLE jdbcTable (column, column2, ...) loads JDBC source data positionally for only the specified target table columns. If the JDBC source has fewer columns than the input table, then those columns are empty in the inserted rows.

    This statement loads the first three columns from the JDBC countries table into the Name, Continent, and Region columns of the Countries table. Even if the table stores these columns in a different order, or if there are columns in between the three shown here, LOAD DATA still loads data only into Name, Continent, and Region.

    LOAD DATA FROM JDBC CONNECTION MyConnection
    TABLE countries
    INTO Sample.Countries (Name,Continent,Region)
  • LOAD DATA FROM JDBC CONNECTION connection TABLE jdbcTable ... INTO table ... VALUES (header,header2 ...) loads data form the JDBC source for only the columns that have the header names specified in the VALUES clause. Using this syntax, you can place column data at any position in the JDBC source table into columns at any position in the target table.

    The number of columns in the INTO table clause must match the number of headers in the VALUES clause. If the INTO table clause does not specify any columns, then the number of headers in the VALUES clause must match the number of the headers in the table. In this case, the source data is loaded positionally into the table.

    This statement loads data from the name, surface_area, and region columns of the JDBC countries table into the corresponding columns of the Sample.Countries table.

    LOAD DATA FROM JDBC CONNECTION MyConnection
    TABLE countries
    INTO Sample.Countries (Name,SurfaceArea,Region)
    VALUES (name,surface_area,region)

    The way the VALUES clause matches SQL column names positionally is similar to INSERT command syntax.

Arguments

filePath

The server-side location of a text file containing the data to load, specified as a complete file path enclosed in quotes.

  • Each line in the file specifies a separate row to be loaded into the table. Newline ("\n") is the default line separator. Blank lines are ignored. You can define a different line separator by specifying the lineseparator option in the USING jsonOptions clause.

  • Data values in a row are separated by a column separator character. Comma is the default column separator character. All data fields must be indicated by column separators, including unspecified data indicated by placeholder column separators. You can define a different column separator character by specifying the columnseparator option in the USING jsonOptions clause.

  • By default, no escape character is defined. To include the column separator character as a literal in a data value, enclose the data value with quotation marks. To include a quotation mark in a quoted data value, double the quote character (""). You can define an escape character specifying the escapechar option in the USING jsonOptions clause.

  • All data in a data file record is validated against the table’s data criteria, including the number of data fields in the record, and the data type and data length of each field. A data file record that fails validation is passed over (not loaded). No error message is issued. Data loading continues with the next record.

  • By default, data values are specified in the order of the fields in the table (or view). You can use the COLUMNS clause to specify the data in a different order. You can use a view to load a data record to a table by supplying only values for the fields that are defined in the view.

table

The table to load the data into. A table name can be qualified (schema.tablename), or unqualified (tablename). An unqualified table name takes the default schema name. You can specify a view to load data in the table accessed through the view.

column

The table columns to load file data into, specified in the order of the columns in the file. This list of column names enables you to specify selected table columns and to match the order of the data file items to the columns in table. Unspecified columns that are defined in the table take their default values. If this clause is omitted, all user-defined fields in table must be represented in the data file.

header

A comma-separated list of header values used to identify columns to load from the data source.

  • When loading data from a file source that does not contain a header row, specify headers in the COLUMNS header type, header2 type2, ...) clause to name the columns.

    • If you include a VALUES clause, specify these header names in VALUES (header, header2) to select which columns to load into the table.

    • If you do not include a VALUES clause, then these header names must match the column names in the target table.

  • When loading data from a file source that contains a header row, specify headers in the VALUES (header, header2) clause to identify which headers in the source file to load data from. These header names must exist in the source file.

  • When loading data from a JDBC source, specify headers in the VALUES (header, header2) clause to identify which columns in the JDBC source table to load data from. These header names must exist in the JDBC source table.

type

The data type of the headers specified in the COLUMNS header type, header2 type2, ...) clause. The data type for each column must be compatible with the table’s data type. The table’s data length, not the COLUMNS data length, is used to validate the data.

jsonOptions

Loading options, specified in the USING clause as a JSON (JavaScript Object Notation) object or a string containing a JSON object. These syntaxes are equivalent:

USING {"from":{"file":{"header":true}}}
USING '{"from":{"file":{"header":true}}}'

Use these JSON objects to set loading options that cannot be set using SQL keywords. Specify these objects using nested key:value pair syntax, as described in JSON Values.

The primary use of this object is to set options of the loaded data that supplements the FROM FILE syntax. This example shows a sample JSON object with multiple options specified. The whitespace shown here is optional and is provided for readability only.

USING
{
  "from": {
    "file": {
       "header": true,
       "skip": 2
       "charset": "UTF-8"
       "escapechar": "\\"
       "columnseparator": "\t"
    }
  }
}

This table shows the options that you can specify. Unspecified options use the default values.

Option Description Example
from.file.lineseparator
Specify the line separation character as one these values:
  • \n — New line
  • \r — Carriage return
  • \n\r — New line followed by a carriage return
Each line in the data file is loaded into the table as a separate row. Blank lines are ignored.
Default: \n
{"from":{"file":{"lineseparator":"\r"}}}
from.file.header
Set to true (1) to indicate that the first line of the source file is a header row. Column names in this header can then be specified and used in a VALUES clause, if no COLUMNS clause is specified. For more details, see Load from File with Headers and Specify Options.
Default: false (0)
{"from":{"file":{"header":true}}}
from.file.skip
Specify the number of lines at the start of the file to skip. If header is set to true, then skip indicates the number of lines to skip in addition to the header.
Default: 0
{"from":{"file":{"skip":2}}}
from.file.charset
Specify the character set used to parse input data.
Default: LOAD DATA uses the character set of the host operating system.
{"from":{"file":{"charset":"UTF-8"}}}
from.file.escapechar
Specify the escape character used for literal values, such as column separator characters that are used within a column value.
Default: None
{"from":{"file":{"escapechar":"\\"}}}
from.file.columnseparator
Specify the column separator character.
Default: ","
{"from":{"file":{"columnseparator":";"}}}

jdbcConnection

A defined SQL Gateway Connection name use to load data from a JDBC source. For details on establishing a JDBC connection, see Connecting the SQL Gateway via JDBC.

jdbcTable

The external SQL data source table accessed over a JDBC connection. For details on establishing a JDBC connection, see Connecting the SQL Gateway via JDBC.

Examples

Load Data From CSV File into SQL Tables and Views

This example shows how to load data stored in a comma-separated value (CSV) file into an existing table and view.

Create the table to load data into. This table contains three fields specifying membership data: a member ID, the membership term length in months, and the US state where the member lives, using the two-character state abbreviation.

CREATE TABLE Sample.Members (
    MemberId INT PRIMARY KEY,
    MemberTerm INT DEFAULT 12,
    MemberState CHAR(2))

Copy these data records into a text file. Save the file on your local machine and name it members.csv. This file specifies membership IDs and member state values. The second row is missing a value, as indicated by a placeholder comma inserted before where the value would be.

6138830,MA
1720936,
4293608,NH

Use LOAD DATA to load the data into the Sample.Members table. Replace the path shown here with the path where you saved the file.

LOAD DATA FROM FILE 'C://temp/members.csv' INTO Sample.Members (MemberId,MemberState)

Examine the data. The MemberId and MemberState columns have been populated. The source file did not contain data for the MemberTerm column, so these column values default to 12. The missing row value is loaded in as a NULL value.

SELECT * FROM Sample.Members
MemberId MemberTerm MemberState
6138830 12 MA
1720936 12  
4293608 12 NH

LOAD DATA does not report an SQLCODE error for the missing data because the overall SQLCODE result of LOAD DATA is 0 (success). A LOAD DATA operation is considered successful if:

  • LOAD DATA can access the source.

  • The target table exists.

  • The LOAD DATA operation is valid. For example, the operation specifies the correct number of columns and the column names exist in the target table.

To view the SQLCODE errors for individual rows, along with other information about the LOAD DATA operation, you can use the %SQL_Diag.Result and %SQL_Diag.Message tables. For more details, see View Diagnostic Logs of Loaded Data.

View the messages from the most recent LOAD DATA operation. The row with the missing state abbreviation reports an SQLCODE error of -104. The results shown are truncated for readability.

SELECT actor,message,severity,sqlcode
FROM %SQL_Diag.Message
WHERE diagResult = 
  (SELECT TOP 1 resultId
  FROM %SQL_Diag.Result
  ORDER BY resultId DESC)
actor message severity sqlcode
server {"resultid":"1","bufferrowcount":500, ... } info 0
FileReader
Reader Complete: Total Input file read time: 23 ms,
completed 0
JdbcWriter
[SQLCODE: <-104>:<Field validation failed in INSERT>] [%msg: ... (Varchar Value: 'state...' Length: 5) > maxlen: (2)>]
error -104
JdbcWriter
Writer Complete: Total write time: 72 ms,
completed 0

If you create a view from a table, you can also load data into the table by using the view. Create a view that shows only the membership ID and state columns of the Sample.Members table.

CREATE VIEW Sample.VMem (MId,State) AS SELECT MemberId,MemberState FROM Sample.Members

Copy these additional data records into a text file. Save the file on your local machine and name it members2.csv.

6785674,VT
4564563,RI
4346756,ME

Use LOAD DATA to load this new CSV data into the table by using the view you created.

LOAD DATA FROM FILE 'C://temp/members2.csv' INTO Sample.VMem(MId,State)

View the data returned by the view, which includes the data from both loaded CSV files.

SELECT * FROM Sample.VMem
MId State
6138830 MA
1720936  
4293608 NH
6785674 VT
4564563 RI
4346756 ME

View the data in the base table, which includes combined column data from both CSV files. The default value of 12 is applied to the values in the MemberTerm column for the second loaded CSV file as well.

SELECT * FROM Sample.Members
MemberId MemberTerm MemberState
6138830 12 MA
1720936 12  
4293608 12 NH
6785674 12 VT
4564563 12 RI
4346756 12 ME

Delete the view and table.

DROP VIEW Sample.VMem
DROP TABLE Sample.Members

Troubleshooting

View Diagnostic Logs of Loaded Data

Each call to LOAD DATA generates a new row in the %SQL_Diag.Result table. This table contains diagnostic information about the operation. You can view these rows by using a SELECT query. For example, this query returns the five most recent LOAD DATA calls.

SELECT TOP 5 * FROM %SQL_Diag.Result ORDER BY createTime DESC

The returned table has these columns:

  • createTime — Timestamp for when the LOAD DATA operation took place and the log entry row was created. Timestamps are in UTC (Coordinated Universal Time), not local time.

  • errorCount — The number of errors that occurred. Includes errors that cause LOAD DATA command and failures to load or write individual rows of data.

  • inputRecordCount — Number of records successfully loaded.

  • maxErrorCount — Maximum number of row insertion errors that LOAD DATA tolerates before failing the operation.

  • namespace — Namespace in which the LOAD DATA operation took place.

  • processId — Integer ID of the process that performed the LOAD DATA operation.

  • resultId — Integer ID of the log entry. This value is the primary key of the table.

  • sqlCode — SQLCODE of the overall LOAD DATA operation.

  • user — User who performed the LOAD DATA operation.

The %SQL_Diag.Message table provides detailed message data for each LOAD DATA operation logged in the %SQL_Diag.Result table. The diagResult column of %SQL_Diag.Message is a foreign key reference to the resultId column of %SQL_Diag.Result table, enabling you to access messages for individuals LOAD DATA operations.

For example, this query returns all error messages associated with the LOAD DATA operation that has a resultId of 29. You can use this data to diagnose which rows of the table failed to load.

SELECT *
FROM %SQL_Diag.Message
WHERE severity = 'error'
AND diagResult = 
  (SELECT resultID
  FROM %SQL_Diag.Result
  WHERE ResultId = 29)

The returned table has these columns:

  • actor — Entity that reported the message, such as server, FileReader, or JdbcWriter.

  • diagResult — Row ID of the LOAD DATA log entry recorded in the %SQL_Diag.Result table.

  • message — Message data. For errors, this column includes SQLCODE values and %msg text.

  • messageTime — Timestamp of the message in UTC (Coordinated Universal Time), not local time.

  • severity — Level of severity of the message. severity is a logical integer that has a correspond display. Valid values: 0 ("completed"), 1 ("info"), 2 ("warning"), 3 ("error"), 4 ("abort").

  • sqlcode — SQLCODE of the message. Messages with a severity of 0 ("completed") or 1 ("info") report an SQLCODE of 0. Messages with a severity of 2 ("warning") or 3 ("error") report the SQLCODE associated with that message. Messages with a severity of 4 ("abort") report an SQLCODE of -400.

See Also

FeedbackOpens in a new tab