T-SQL (mssql): INSERT INTO .... OUTPUT ... INTO

[Quido]

Hello,

can you help me with using construction that return ID from INSERT command on mssql server?

INSERT INTO .... OUTPUT ... INTO

Oracle using RETURNING clause, there is no problem here:

Query.SQL.Text:='INSERT INTO table (col1, col2) VALUES (expr1, expr2) RETURNING col1 INTO :kod';
Query.ParamByName('kod').ParamType:=ptInputOutput;
Query.ParamByName('kod').AsInteger:=0;
Query.prepare;
Query.execute;
Result:=Query.ParamByName('kod').Text;

but with mssql

Query.SQL.Text:='INSERT INTO table (col1, col2) OUTPUT inserted.col1 INTO :kod VALUES (expr1, expr2)';

give error: Statement could not be prepared. Must declare the table variable @p1.

Can you help me?

[azyk]

Hello,

To retrieve the ID field value after inserting a record, you can use the SCOPE_IDENTITY() T-SQL function in a SQL query.
For example:

Code: Select all

  Query.SQL.Text :=
  ' INSERT INTO table ' +
  '   (col1, col2) ' +
  ' VALUES ' +
  '   (expr1, expr1) '+
  ' SET :kod = SCOPE_IDENTITY()';