Skip to main content
InterSystems IRIS for Health 2025.1
AskMe (beta)
Loading icon
Responses may be inaccurate. Learn more about AskMe.

Importing SQL Code

This topic describes how to import SQL code from a text file into InterSystems SQL. When you import SQL code, InterSystems IRIS® data platform prepares and executes each line of SQL. If it encounters a line of code it cannot parse, SQL import skips over that line of code and continues to prepare and execute subsequent lines until it reaches the end of the file. All SQL code import operations import to the current namespace.

SQL Import is primarily used to import Data Definition Language (DDL) commands, such as CREATE TABLE, and to populate tables using INSERT, UPDATE, and DELETE commands.

InterSystems recommends importing SQL code from another system or vendor with the LOAD SQL command. However, you can choose to invoke either the ImportDDL()Opens in a new tab or Run()Opens in a new tab methods from the %SYSTEM.SQL.SchemaOpens in a new tab class as well.

Note:

It is worth noting that the %SYSTEM.SQL.SchemaOpens in a new tab class also allows for exporting DDL commands using the ExportDDL() method. In brief, this method allows you to export a DDL script file that can later be imported via methods described in this page.

Importing SQL with LOAD SQL

With the LOAD SQL command, you can easily and efficiently load schemas and table definitions from one database to another, regardless of the system it originates from, as well as run standard DML operations, like INSERT. Any errors caused by the loading are written to the %SQL_Diag.Result log. The command can load DDL statements from a single file or from all the files within a single directory (including any subdirectories).

SQL commands should be stored in a standard, non-formatted file (like a text file). Commands may span multiple lines, but are differentiated from each other by a common delimiter that indicates where one command ends and another begins. The default delimiter differs depending on the origin dialect of the commands. See the LOAD SQL page for more information on delimiters and supported dialects.

Importing InterSystems SQL with ObjectScript

You can import InterSystems SQL code from a text file using either of the following %SYSTEM.SQL.SchemaOpens in a new tab methods:

  • ImportDDL()Opens in a new tab is a general-purpose SQL import method. This method runs as a background (non-interactive) process. To import InterSystems SQL you specify “IRIS” as the third parameter.

  • Run()Opens in a new tab is an InterSystems SQL import method. This method runs interactively from the Terminal. It prompts you to specify the location of the import text file, the location to create the Errors.log file and the Unsupported.log file, and other information.

Note:

This import and execution of SQL DDL code should not be confused with the Import Statements Action from the Management Portal SQL interface. That operation imports SQL Statements in XML format.

The following example imports the InterSystems IRIS SQL code file pathname mysqlcode.txt, executing the SQL commands listed in that file in the current namespace:

  DO $SYSTEM.SQL.Schema.ImportDDL("c:\InterSystems\mysqlcode.txt",,"IRIS")

By default, ImportDDL() creates an errors log file, as specified in the second parameter. This example, which omits the second parameter, creates by default a file named mysqlcode_Errors.log in the same directory as the SQL code file. This log file is created even when there is nothing written to it.

When executing ImportDDL() from the Terminal, it first lists the input file, then the error log file, then lists each SQL command imported, as shown in the following example:

Importing SQL Statements from file: c:\InterSystems\mysqlcode.txt
 
Recording any errors to principal device and log file: c:\InterSystems\mysqlcode_Errors.log

 SQL statement to process (number 1):
     CREATE TABLE Sample.NewTab (Name VARCHAR(40))
      Preparing SQL statement...
      Executing SQL statement...
  DONE
 
  SQL statement to process (number 2):
     CREATE INDEX NameIDX ON Sample.NewTab (Name)
      Preparing SQL statement...
      Executing SQL statement...
  DONE
 
Elapsed time: 7.342532 seconds

If an error occurs in any SQL command, the Terminal display the error, as shown in the following example:

SQL statement to process (number 3):
       INSERT INTO Sample.MyStudents (StudentName,StudentDOB) SELECT Name,
       DOB FROM Sample.Person WHERE Age <= '21'
    Preparing SQL statement...
ERROR #5540: SQLCODE: -30 Message:  Table 'SAMPLE.PERSON' not found
       Pausing 5 seconds - read error message!  (Type Q to Quit)

If you do not Quit within 5 seconds, ImportDDL() proceeds to execute the next SQL command. The error is recorded in the errors log file with a timestamp, the user name, and the namespace name.

Import File Format

An SQL text file must be an unformatted file, such as a .txt file. Each SQL command must begin on its own line. An SQL command may be broken into multiple lines and indentation is permitted. Each SQL command must be followed by a GO statement on its own line.

The following is an example of a valid InterSystems SQL import file text:

  CREATE TABLE Sample.MyStudents (StudentName VARCHAR(32),StudentDOB DATE)
GO
  CREATE INDEX NameIdx ON TABLE Sample.MyStudents (StudentName)
GO
  INSERT INTO Sample.MyStudents (StudentName,StudentDOB) SELECT Name,
  DOB FROM Sample.Person WHERE Age <= '21'
GO
  INSERT INTO Sample.MyStudents (StudentName,StudentDOB) 
          VALUES ('Jones,Mary',60123)
GO
  UPDATE Sample.MyStudents SET StudentName='Smith-Jones,Mary' WHERE StudentName='Jones,Mary'
GO
  DELETE FROM Sample.MyStudents WHERE StudentName %STARTSWITH 'A'
GO

Supported SQL Commands

Not all valid InterSystems SQL commands can be imported. The following is a list of supported InterSystems SQL commands:

  • 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

Importing non-InterSystems SQL with ObjectScript

SQL import can be used to import InterSystems SQL code or to import SQL code from other vendors (FDBMS, Informix, InterBase, MSSQLServer, MySQL, Oracle, Sybase). Code from other vendors is converted to InterSystems SQL code and executed. InterSystems SQL import utilities only import commands and clauses that are compatible with the InterSystems IRIS implementation of the SQL standard; incompatible commands are commonly parsed, but ignored. The following methods are provided:

  • ImportDDL()Opens in a new tab is a general-purpose SQL import method. This method runs as a background (non-interactive) process. Refer to Importing InterSystems SQL for general information on using this method.

    To import SQL in a specific format you specify the name of that format as the third parameter: FDBMS, Informix, InterBase, MSSQLServer (or MSSQL), MySQL, Oracle, or Sybase.

    The following example imports the MSSQL code file mssqlcode.txt, executing the SQL commands listed in that file in the current namespace:

      DO $SYSTEM.SQL.Schema.ImportDDL($lb("C:\temp\somesql.sql","UTF8"),,"MSSQL")

    Note that if the third parameter is MSSQL, Sybase, Informix, or MySQL, the first parameter can be either an SQL code file pathname or a two-element %List with the first element the SQL code file pathname and the second element is the I/O translation table to use.

  • Individual interactive methods are provided in %SYSTEM.SQL.SchemaOpens in a new tab to import the following types of SQL: LoadFDBMS()Opens in a new tab, LoadInformix()Opens in a new tab, LoadInterBase()Opens in a new tab, LoadMSSQLServer()Opens in a new tab, LoadOracle()Opens in a new tab, and LoadSybase()Opens in a new tab. These methods runs interactively from the Terminal. It prompts you to specify the location of the import text file, the location to create the Errors.log file and the Unsupported.log file, and other information.

  • ImportDDLDir()Opens in a new tab allow you to import SQL code from multiple files in a directory. This method runs as a background (non-interactive) process. It supports Informix, MSSQLServer, and Sybase. All files to be imported must have a .sql extension suffix.

  • ImportDir()Opens in a new tab allow you to import SQL code from multiple files in a directory. Provides more options than ImportDDLDir(). This method runs as a background (non-interactive) process. It supports MSSQLServer, and Sybase. You can specify a list of allowed file extension suffixes.

Importing and Exporting SQL DataNext section
Previous sectionSQL Settings Reference
FeedbackOpens in a new tab