LOAD SQL (SQL)

Loads external files containing DDL statements into your relational database structure.

Synopsis

Load from a Single File

LOAD SQL FROM FILE filename 
  [ DIALECT language ]
  [ DELIMITER delim ]
  [ VERBOSE ]
  [ LOG TO FILE [ logfile ] ]
  [ IGNORE ERRORS ]

Load from a Directory

LOAD SQL FROM DIRECTORY dir [ RECURSE ]
  [ DIALECT language ]
  [ DELIMITER delim ]
  [ VERBOSE ]
  [ LOG TO FILE [ logfile ] ]
  [ IGNORE ERRORS ]

Description

A LOAD SQL command imports one or more files that contain SQL statements and performs the commands listed in order. It can be used to load an exported relational structure from one system to another with ease and efficiency, as well as to insert data into the relational structure with INSERT statements. These statements can be loaded either from a single file or from a set of files contained with a directory (potentially including any subdirectories). Due to the command’s support of different SQL dialects, such as ORACLE, InterSystems recommends using this command when migrating to InterSystems IRIS.

The following is a complete list of commands that LOAD SQL can import:

CREATE TABLE, ALTER TABLE, DROP TABLE
CREATE VIEW, ALTER VIEW, DROP VIEW
CREATE INDEX all index types, except bitslice
CREATE USER, DROP USER
CREATE ROLE
GRANT, REVOKE
INSERT, UPDATE, INSERT OR UPDATE, DELETE
SET OPTION
SELECT for optimizer plan mode only

By default, the %SQL_Diag.Result log stores messages indicating the success of the loading or not.

If you specify the VERBOSE option, the information written into the log file is also written in the terminal.

If you specify the IGNORE ERRORS option, then every statement in the file is processed, regardless of whether individual statements fail execution. Omitting the option causes the LOAD SQL command to fail at the first statement in the file that fails execution.

Arguments

filename

The path from the root directory that specifies the file that should be loaded, delimited with single quotes (').

The file specified by filename should be an unformatted file, such as a .txt file. Each SQL command must begin on its own line, though commands may be broken up between multiple lines, and separated with a specified delimiter.

language

The dialect that the loaded statements are written in. Possible values are: IRIS, CACHE, FDBMS, INFORMIX, INTERBASE, MSSQLSERVER, MSSQL, ORACLE, and SYBASE.

delim

The character used to separate statements within the loaded file.

If no delim is specified, the default delimiter is determined by the dialect specified by language. In particular, if the dialect is INFORMIX, INTERBASE, or ORACLE, the default delimiter is ;. If the dialect is IRIS, CACHE, SYBASE, or MSSQL, the default delimiter is GO.

logfile

The name of the file used to log issues raised when loading statements.

When the logfile is not specified, logs are stored in %SQL_Diag and are written into a file stored in the same directory as filename. This file has the same name as filename, minus the file extension, with _Errors.log appended to it. For example, if your input file was sampleddl.txt, the log file would be named sampleddl_Errors.log and would be saved in the same directory as sampleddl.txt.

dir

The path from the root directory to the directory that contains the files for loading.

If you specify the RECURSE keyword, files stored in the subdirectories of the specified directory will also be loaded.

Examples

The following example demonstrates the how the LOAD SQL command is used to load DDL from the INFORMIX dialect from the exported_ddl.sql file. The contents of the loaded file can be found in the Loaded File tab.

LOAD SQL FROM FILE 'C:/stored/exported_ddl.txt'
  DIALECT IRIS
  LOG TO FILE 'C:/logs'
  IGNORE ERRORS

  CREATE TABLE Sample.Students (StudentName VARCHAR(32),StudentDOB DATE,FavoriteClass VARCHAR(16))
GO
  CREATE INDEX FavClassIdx ON TABLE Sample.MyStudents (FavoriteClass)
GO
  CREATE TABLE Sample.Teachers (TeacherName VARCHAR(32),TeacherDOB DATE,Subject VARCHAR(16))
GO
  CREATE INDEX SubIdx ON Sample.Teachers (Subject)
GO
  INSERT INTO Sample.Students (StudentName,StudentDOB,FavoriteClass) VALUES ('John Doe',TO_DATE('01 JAN 1999'),'Spanish')
GO
  INSERT INTO Sample.Teachers (TeacherName,TeacherDOB,Subject) VALUES ('Jane Doe',TO_DATE('02 FEB 1970'),'Calculus')