1

with FireDac, How to get last inserted id on ms sql server? thanks

Arioch 'The
  • 15,799
  • 35
  • 62
ae1080
  • 374
  • 2
  • 7
  • 14

2 Answers2

5

Make the that id to a identity column and then get it by Using 

SELECT SCOPE_IDENTITY()

after the insert statement

Please refer the following links

http://msdn.microsoft.com/en-us/library/ms190315.aspx

Thangamani Palanisamy
  • 5,152
  • 4
  • 32
  • 39
  • 1
    This only works on Microsoft SQL Server, not other SQL Servers like PostgrSQL, Oracle, MySQL etc. Rather use the [GetLastAutoGenValue](http://docwiki.embarcadero.com/Libraries/Berlin/en/FireDAC.Comp.Client.TFDCustomConnection.GetLastAutoGenValue) method on the TFDConnection – Reversed Engineer Feb 10 '17 at 18:39
2

Use auto-incremented field type

http://www.da-soft.com/anydac/docu/Auto-Incremental_Fields.html

This would provide for code like 

DataSet.Insert;
.... 
DataSet.Post; 
id := DataSet.FieldByName('ID').AsInteger;

Another approach might be crafting proper SQL statements like described at

AnyDAC author also suggests a special method to fetch DBMS-specific toolings via http://docs.embarcadero.com/products/rad_studio/firedac/uADCompClient_TADCustomConnection_GetLastAutoGenValue@String.html

But all those post-factum requests with SELECT @@identity or SELECT SCOPE_IDENTITY are fragile and dangerous. When you insert data into table A, its triggers may insert data into related tables B and C, and identity would recall C's autoinc, rather than table where you started inserting at.

Community
  • 1
  • 1
Arioch 'The
  • 15,799
  • 35
  • 62
  • 1
    Well, but how do you get the last `IDENTITY` value if the `INSERT` query doesn't return dataset for instance ? I mean, does a dataset class contain a property for getting last `IDENTITY` value (if you use auto-incremental field) ? In other words, how do you get that value from dataset descendant component ? – TLama Apr 11 '13 at 13:02
  • @TLama `DataSet.Insert; .... DataSet.Post; id := DataSet.FieldByName('ID').AsInteger;` – Arioch 'The Apr 11 '13 at 13:03
  • So using auto-incremental field enforce the first recordset return identity column even if you don't want it ? – TLama Apr 11 '13 at 13:07
  • @TLama what's wrong with that? You anyway would get the result packet (success/failure) back from server, now that packet would include extra 4 bytes. Those are not extra round-trips that are required for separate SQL requests. – Arioch 'The Apr 11 '13 at 13:15
  • If I would like to get that value, I would ask for it by a `SELECT` query. This is quite out of your control. What if you'll want to `INSERT` a record and `SELECT` some data at once ? You'll need to take that recordset from a second dataset of a resultset ? That's annoying... – TLama Apr 11 '13 at 13:24
  • @TLama what-if is a rather vague approach. You can always devise a situation that is against any given idea. The topic starter did not told about doing anything *after* inserting values, neither issuing `select` nor `update` nor shutting down server. Nor did he told he does not need inserted IDs, he does need them. So while you what-ifs are valid per se, i fail to see how they relate to this particular question. – Arioch 'The Apr 11 '13 at 13:29
  • I'm not talking about this Q&A. It's my opinion on how the auto-incremental field behaves. I like things under my control and if I don't query for `IDENTITY` I don't want to get it in a resultset. – TLama Apr 11 '13 at 13:41
  • @TLama then don't make it autoinc but integer, or set Client-side inc or make manual INSERT statement instead of TDataSet.Insert – Arioch 'The Apr 11 '13 at 13:57