9.78.5 SQL Statements

The format of the SQL statements differs depending on which database type you are using.

For MySQL, Netezza, PostreSQL, and SybaseIQ, the path to the file (stated as <path to file> in the code blocks below) can be entered in three different ways:

  • Entering the path explicitly, e g 'Users/mine/mypath'
  • Stating the UDR field containing the path by entering $(UDR.fullpath)
  • Stating the MIM value containing the path, e g $(Source_Pathname) for the Disk agent

MySQL

For remote loading (the file resides in a local directory)

LOAD DATA LOCAL INFILE <path to file> INTO TABLE TABLENAME FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

For server side loading (file resides in the server file system of the database)

LOAD DATA INFILE <path to file> INTO TABLE TABLENAME FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Netezza

For remote loading (the file resides in a local directory)

INSERT INTO TABLENAME SELECT * FROM EXTERNAL <path to file> USING (delim ',' REMOTESOURCE 'JDBC')

For server side loading (file resides in the server file system of the database)

INSERT INTO TABLENAME SELECT * FROM EXTERNAL <path to file> USING (delim ',') 

PostgreSQL

For server side loading (file resides in the server filesystem of the database)

COPY table_name [(column1,colunm2,...,columnN)]  FROM <path to file> DELIMITER ',' csv;

Stating columns is optional.

Postgres does not support remote file loading.

SAP HANA

For server side loading (file resides in the server file system of the database)

  1. Create a control file containing code below (this example ctl file name is abc.ctl, abc.txt is the csv file): 

    import data into table SYSTEM."TEST_LOADER" from 'abc.txt' record delimited by '\n' fields delimited by ',' optionally enclosed by '"' error log 'abc.err'

     

  2. Run the workflow with the following command: 

    import from '/<path>/abc.ctl'

SAP HANA does not support remote file loading. 

Sybase IQ

For server side loading (file resides in the server file system of the database)

LOAD TABLE TABLENAME (COLUMNNAME, COLUMNNAME2) USING FILE <path to file> FORMAT BCP ESCAPES OFF 
DELIMITED BY ','

The Sybase JConnect driver does not support remote file loading.