Devart Forums

Posted: **Thu 26 Jun 2014 07:26**

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?

Posted: **Thu 26 Jun 2014 10:25**

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()';

Devart Forums

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

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

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