HPL/SQL - Procedural SQL on Hadoop, NoSQL and RDBMS

This is an old revision of the document!

---

====== INSERT Statement - PL/HQL ====== INSERT statement inserts rows into a table. **Syntax**: Insert from SELECT: <code language=sql> INSERT OVERWRITE TABLE table_name select_statement | INSERT INTO [TABLE] table_name select_statement </code> Insert values: <code language=sql> INSERT INTO [TABLE] table_name VALUES (exrp, expr2, ...) [, (exrp, expr2, ...), ...] </code> ===== INSERT VALUES ===== PL/HQL provides you with two options to run INSERT VALUES statement: **native** and **select**. Use the [[configuration#plhqlinsertvalues|plhql.insert.values]] option to define how to handle INSERT VALUES statement, the default value is **native**. ==== Native INSERT VALUES ==== If [[configuration#plhqlinsertvalues|plhql.insert.values]] is set to **native** PL/HQL relies on the database that must support INSERT VALUES syntax. Note that INSERT VALUES is available in Hive since version 0.14 only and can only be performed on tables that support ACID. ==== Transform into INSERT SELECT ==== If [[configuration#plhqlinsertvalues|plhql.insert.values]] is set to **select** PL/HQL transforms VALUES clause to the list of SELECT FROM dual UNION ALL ... clauses. For example, if you execute the following INSERT statement: <code language=sql> INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON'); </code> PL/HQL transforms it to: <code language=sql> INSERT INTO dept SELECT 10, 'ACCOUNTING', 'NEW YORK' FROM default.dual UNION ALL SELECT 20, 'RESEARCH', 'DALLAS' FROM default.dual UNION ALL SELECT 30, 'SALES', 'CHICAGO' FROM default.dual UNION ALL SELECT 40, 'OPERATIONS', 'BOSTON' FROM default.dual; </code> You can specify the single row, single column dual table using [[configuration#plhqldualtable|plhql.dual.table]] option. **Important Note:** Such approach leads to creating one HDFS file **per** INSERT statement, and must not be used to insert large number of rows. **Compatibility:** Oracle, Microsoft SQL Server, IBM DB2, Teradata, PostgreSQL and MySQL **Version**: PL/HQL 0.03