IDENTITY Column INSERT with OVERRIDING VALUE.sql

-- this syntax is used for testing the INSERT with OVERRIDING {SYSTEM|USER} VALUE feature for an IDENTITY column in SAP HANA

CREATE COLUMN TABLE BOARDMEMBERS 
(
BMID INTEGER,
BM VARCHAR(20),
ROLE VARCHAR(20),
SALARY INTEGER
);
INSERT INTO BOARDMEMBERS VALUES (1, 'Bob', 'CEO', 1500000);
INSERT INTO BOARDMEMBERS VALUES (2, 'Denys', 'CTO', 1000000);


CREATE COLUMN TABLE EMPLOYEES 
(
EMPID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY (start with 100),
EMP VARCHAR(20),
ROLE VARCHAR(20),
SALARY INTEGER
);

INSERT INTO EMPLOYEES VALUES('Christiano','Manager',500000);
INSERT INTO EMPLOYEES VALUES('Eric','Manager',350000);
INSERT INTO EMPLOYEES VALUES('Ryan','Director',450000);
INSERT INTO EMPLOYEES VALUES('Bryan','Director',150000);
INSERT INTO EMPLOYEES VALUES('Doreen','Manager',500000);
INSERT INTO EMPLOYEES VALUES('Gabriel','Manager',350000);
INSERT INTO EMPLOYEES VALUES('Angel','Director',450000);
INSERT INTO EMPLOYEES VALUES('Robin','Director',150000);
INSERT INTO EMPLOYEES VALUES('Sarah','Manager',500000);
INSERT INTO EMPLOYEES VALUES('Peter','Manager',350000);

SELECT * FROM EMPLOYEES;

-- the following will return an error due to too many values
INSERT INTO EMPLOYEES (SELECT * FROM BOARDMEMBERS WHERE BMID = 2);

-- this is one way to insert values from another table into a table with an identity
INSERT INTO EMPLOYEES (SELECT BM, ROLE, SALARY FROM BOARDMEMBERS WHERE BMID = 2);
-- this is another way assuming that both tables share the same definition
INSERT INTO EMPLOYEES OVERRIDING USER VALUE (SELECT * FROM BOARDMEMBERS WHERE BMID = 2);
SELECT * FROM EMPLOYEES;

INSERT INTO EMPLOYEES OVERRIDING SYSTEM VALUE (SELECT * FROM BOARDMEMBERS WHERE BMID = 1);
SELECT * FROM EMPLOYEES;