INSERT STATEMENT

Description

The INSERT statement inserts rows into a table, nickname, or view, or the underlying tables, nicknames, or views of the specified fullselect.

Use of query as insert target name is not supported in Snowflake. The output query can be malformed.

Use of view name as insertion name is not supported in Snowflake. The output query can be malformed.

The include columns is not applicable in Snowflake. The output query can be malformed.

Click here to navigate to the IBM DB2 docs page for this syntax.

Grammar Syntax

Sample Source Patterns

IBM DB2

INSERT INTO SOMEOBJECT1 (COL1, COL2) VALUES (NULL,DEFAULT);
INSERT INTO SOMEOBJECT2 (COL1, COL2) VALUES (NULL,DEFAULT) WITH RR;
INSERT INTO SOMEOBJECT3 (COL1, COL2) VALUES (NULL,DEFAULT) WITH RS;
INSERT INTO SOMEOBJECT4 (COL1, COL2) VALUES (NULL,DEFAULT) WITH CS;
INSERT INTO SOMEOBJECT5 (COL1, COL2) VALUES (NULL,DEFAULT) WITH UR;
INSERT INTO SOMEOBJECT6 VALUES (DEFAULT), (DEFAULT), (DEFAULT), (DEFAULT);

-- NOT SUPPORTED CASES
CREATE VIEW VIEW1 AS SELECT * FROM T;
INSERT INTO VIEW1 (COL1, COL2) VALUES (NULL,DEFAULT);
INSERT INTO (SELECT * FROM SOMEOTHERTABLE) VALUES (DEFAULT);

INSERT INTO SOMEOBJECT (COL1, COL2)
INCLUDE (COLNAME1 INTEGER, COLNAME2 VARCHAR)
VALUES (DEFAULT,DEFAULT);

Snowflake

INSERT INTO PUBLIC.SOMEOBJECT1 (COL1, COL2) VALUES (NULL,DEFAULT);
INSERT INTO PUBLIC.SOMEOBJECT2 (COL1, COL2) VALUES (NULL,DEFAULT)
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. ISOLATION CLAUSE **
--                                                                  WITH RR
                                                                         ;
INSERT INTO PUBLIC.SOMEOBJECT3 (COL1, COL2) VALUES (NULL,DEFAULT)
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. ISOLATION CLAUSE **
--                                                                  WITH RS
                                                                         ;
INSERT INTO PUBLIC.SOMEOBJECT4 (COL1, COL2) VALUES (NULL,DEFAULT)
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. ISOLATION CLAUSE **
--                                                                  WITH CS
                                                                         ;
INSERT INTO PUBLIC.SOMEOBJECT5 (COL1, COL2) VALUES (NULL,DEFAULT)
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. ISOLATION CLAUSE **
--                                                                  WITH UR
                                                                         ;
INSERT INTO PUBLIC.SOMEOBJECT6 VALUES (DEFAULT), (DEFAULT), (DEFAULT), (DEFAULT);

-- NOT SUPPORTED CASES
CREATE VIEW PUBLIC.VIEW1
AS SELECT * FROM
PUBLIC.T;
-- ** MSC-ERROR - MSCEWI5012 - INVALID NAME AS INSERTION TARGET, USE OF VIEW NAME IS NOT SUPPORTED IN SNOWFLAKE **
--INSERT INTO VIEW1 (COL1, COL2) VALUES (NULL,DEFAULT)

INSERT INTO
-- ** MSC-ERROR - MSCEWI5007 - QUERY AS INSERT TARGET NAME IS NOT SUPPORTED **
--   (SELECT* FROM SOMEOTHERTABLE)
  VALUES (DEFAULT);

INSERT INTO PUBLIC.SOMEOBJECT (COL1, COL2)
-- ** MSC-ERROR - MSCEWI5006 - INTERMEDIATE RESULT TABLE IS NOT SUPPORTED. **
--INCLUDE (COLNAME1 INTEGER, COLNAME2 VARCHAR)
VALUES (DEFAULT,DEFAULT);

Related EWIs

MSCEWI1002: REMOVED STATEMENT, NOT APPLICABLE IN SNOWFLAKE.

MSCEWI5006: INTERMEDIATE RESULT TABLE IS NOT SUPPORTED.

MSCEWI5007: QUERY AS INSERT TARGET NAME IS NOT SUPPORTED.

MSCEWI5012: INVALID NAME AS INSERTION TARGET, USE OF VIEW NAME IS NOT SUPPORTED IN SNOWFLAKE.