SELECT FROM NEW TABLE (INSERT) - IBM DB2 to SQL Server Migration

In IBM DB2, you can use the SELECT FROM NEW TABLE statement to retrieve the identity value generated by an INSERT statement.

In SQL Server, you can use the SCOPE_IDENTITY() function.

IBM DB2:

  -- Sample table
  CREATE TABLE products (id INTEGER GENERATED ALWAYS AS IDENTITY, name VARCHAR(30));
 
  --#SET TERMINATOR @
  SET SERVEROUTPUT ON @
  BEGIN
    DECLARE v_id INTEGER;
 
    -- Insert a new row and get its ID
    SELECT id INTO v_id FROM NEW TABLE (
      INSERT INTO products(name) VALUES ('Apple')
    );
 
    CALL DBMS_OUTPUT.PUT_LINE('Id: ' || v_id);
  END @
  /* Id: 1 */

SQL Server:

  -- Sample table
  CREATE TABLE products (id INTEGER IDENTITY, name VARCHAR(30));
 
  DECLARE @id INTEGER;
 
  -- Insert a new row
  INSERT INTO products(name) VALUES ('Apple');
  -- Get ID
  SET @id = SCOPE_IDENTITY();
 
  PRINT 'Id: ' + STR(@id);
  /* Id: 1 */

For more information, see IBM DB2 to SQL Server Migration.