To retrieve the identity column value or row when inserting more than one row, use the CURSOR statement rather than the INSERT statement. The RETURNING keyword is not necessary in the CURSOR statement because FETCH returns the values:
EXEC SQL DECLARE <cursorname> CURSOR FOR INSERT INTO <table name> SELECT * FROM <table name2>; EXEC SQL Open <cursorname> EXEC SQL Fetch <cursorname> into : hostvariable1 : hostvariable2 : hostvariable3,… EXEC SQL Close <cursorname>