# Getting Previous Add MS SQL Identity Value **URL:** \ **Category:** questions\ **Tags:** mssql\ **Created:** [March 4, 2026, 4:12pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980 "2026-03-04T16:12:02Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![donridley](https://clarionhub.com/user_avatar/clarionhub.com/donridley/32/1459_2.png) [@donridley](https://clarionhub.com/u/donridley)\ **Post date:** [March 4, 2026, 4:12pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/1 "2026-03-04T16:12:02Z")
Good day everyone, I have been researching how to retrive the previously (server side) created auto-numbered/auto-incremented field value in an MS SQL table. I found an example that looked like this: ```auto SomeSQLTable{PROP:SQL} = 'SELECT IDENT_CURRENT(<39>' & SomeSQLTable & '<39>)' Access:SomeSQLTable.Next() Relate:SomeSQLTable.Delete(0) ``` The SELECT IDENT\_CURRENT works and I understand the need for the NEXT but what is the reason for the DELETE (If it’s needed at all.)? Thank you! ---
**Author:** ![Jim-Morgan](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/bc79bd/32.png) [@Jim-Morgan](https://clarionhub.com/u/Jim-Morgan)\ **Post date:** [March 4, 2026, 5:33pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/2 "2026-03-04T17:33:30Z")
I believe that gives you the Identity across all connections/users, not just the record that you created. There’s a help topic in Clarion " Server Side Auto incrementing" that addresses the issue. Otherwise, you can just access the highest numbered identity field and simplfy your code. ---
**Author:** ![donridley](https://clarionhub.com/user_avatar/clarionhub.com/donridley/32/1459_2.png) [@donridley](https://clarionhub.com/u/donridley)\ **Post date:** [March 4, 2026, 6:46pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/3 "2026-03-04T18:46:17Z")
Turns out it is real simple. For example is the first field in the table is: `SysID Long` And there is a primary key associated with that field: `PKSysIDKey` Simply insert an option on the SysID field. `IsIdentity=True` Now, the value of SysID will be available after you perform an Insert or Add. ![ScreenHunter 111](https://clarionhub-uploads.s3.dualstack.eu-north-1.amazonaws.com/original/2X/6/677dc7cb443de4bee2145aaf0587f49f5dee7a91.png) ```clarion SomeTable FILE,DRIVER('MSSQL'),OWNER(Glo:Owner),NAME('dbo.SomeTable'),PRE(Som),CREATE PKSysIDKey KEY(Som:SysID),PRIMARY,NOCASE record RECORD SysID LONG END END ``` ---
**Author:** ![Bruce](https://clarionhub.com/letter_avatar_proxy/v4/letter/b/ccd318/32.png) [@Bruce](https://clarionhub.com/u/Bruce)\ **Post date:** [March 4, 2026, 6:49pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/4 "2026-03-04T18:49:27Z")
The new MSSQL2 driver makes use ofvthe RETURNING clause. When you do an ADD , and the table contains identity [1] fields then those are returned by the INSERT INTO and automatically populated into the record for you. In other words, theres nothing for you to fo, the new value is just “there”. [1] returning works on other fields to. Like ReadOnly and fields-with-defaults-not-included-in-the-add. ---
**Author:** ![Jane](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/ecb155/32.png) [@Jane](https://clarionhub.com/u/Jane)\ **Post date:** [March 4, 2026, 6:54pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/5 "2026-03-04T18:54:41Z")
There’s also a help topic AUTOINCUSESSCOPEIDENTITY. I guess it’s a possible X Y problem, though. What are you trying to accomplish by getting the auto-numbered value? ---
**Author:** ![donridley](https://clarionhub.com/user_avatar/clarionhub.com/donridley/32/1459_2.png) [@donridley](https://clarionhub.com/u/donridley)\ **Post date:** [March 4, 2026, 7:09pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/6 "2026-03-04T19:09:31Z")
I am reading incoming JSON where data is parsed into several MSSQL tables. I needed a way to get the server side auto-numbered value immediately after the ADD. Bruce, That’s pretty cool! ---
**Author:** ![Jane](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/ecb155/32.png) [@Jane](https://clarionhub.com/u/Jane)\ **Post date:** [March 4, 2026, 8:34pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/7 "2026-03-04T20:34:16Z")
I still don’t understand what you’re trying to do, Don. Are you inserting into MSSQL or is a different app doing it? Do you have control over the SQL table (could you use a GUID instead of autonumber?) What are you going to do with the just-inserted identity value when you retrieve it? ---
**Author:** ![urayoan](https://clarionhub.com/user_avatar/clarionhub.com/urayoan/32/1288_2.png) [@urayoan](https://clarionhub.com/u/urayoan)\ **Post date:** [March 5, 2026, 8:12pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/8 "2026-03-05T20:12:24Z")
Did you try this ```auto SELECT SCOPE_IDENTITY(); ``` [How to get the value of autoincrement of last row at the insert](https://stackoverflow.com/questions/13451070/how-to-get-the-value-of-autoincrement-of-last-row-at-the-insert) Cheers ---
**Author:** ![Flavio\_Suarez](https://clarionhub.com/user_avatar/clarionhub.com/flavio_suarez/32/1347_2.png) [@Flavio\_Suarez](https://clarionhub.com/u/Flavio_Suarez)\ **Post date:** [March 6, 2026, 1:34pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/9 "2026-03-06T13:34:55Z")
Hi, I have a similar situation… On the DCT, I’ve set the field with IsIdentity=True Inside the transaction block, after a successful TryInsert(), I use the following code to retrieve the new ID: ```auto myTable{PROP:SQLRowSet} = 'declare @new_id int ' &| ' SELECT @new_id =@@Identity FROM myTable' &| ' SELECT @new_id ' Next(myTable) ``` ---
**Author:** ![JonW](https://clarionhub.com/user_avatar/clarionhub.com/jonw/32/1344_2.png) [@JonW](https://clarionhub.com/u/JonW)\ **Post date:** [March 6, 2026, 1:59pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/10 "2026-03-06T13:59:04Z")
Just in case you are trying to reinvent the wheel badly, maybe google “mssql import json nested tables”, and decide whether what you are doing has any advantage. Even if you eventually want your imported data to have some sort of identity value that is not in the JSON data, the cleanest way is probably to import your JSON data into staging tables, then worry about copying your parentIDs into the children when you do the transfer into your real tables. And that transfer is (IMO) something you would do in a stored procedure, so it all happens in the database. ---
**Author:** ![PurpleEdge2214](https://clarionhub.com/user_avatar/clarionhub.com/purpleedge2214/32/5109_2.png) [@PurpleEdge2214](https://clarionhub.com/u/PurpleEdge2214)\ **Post date:** [March 8, 2026, 2:06am UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/11 "2026-03-08T02:06:24Z")
I thought if you had isIdentity=true in your dct Clarion automatically retrieved the Identity value. Also, I don’t think @@Identity is reliable in a multi-user system? ScopeIdentity is better. ---
**Author:** ![RchdR](https://clarionhub.com/letter_avatar_proxy/v4/letter/r/d78d45/32.png) [@RchdR](https://clarionhub.com/u/RchdR)\ **Post date:** [March 8, 2026, 3:21am UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/12 "2026-03-08T03:21:37Z")
> [@Autoinc in MSSQL Tables](https://clarionhub.com/t/autoinc-in-mssql-tables/5171/3): > > ABProgram.tpw #IF((EXTRACT(UPPER(%FieldUserOptions), UPPER(‘IsIdentity’),1) = ‘1’ OR UPPER(EXTRACT(UPPER(%FieldUserOptions), UPPER(‘IsIdentity’),1)) = ‘TRUE’) OR EXTRACT(UPPER(%FieldUserOptions), UPPER(‘ServerAutoIncColumn’),1) \<\> ‘’) ! EmulateAutoNumKey, this will force the FM to auto insert the record and ! recover the Identity column IF UseType = UseType:Initialize AND ~SELF.Initialized SELF.HasAutoInc = True END #BREAK #ENDIF #IF((EXTRACT(UPPER(%FieldUserOptions), UPPER(‘IsIdentity’… ---
**Author:** ![donridley](https://clarionhub.com/user_avatar/clarionhub.com/donridley/32/1459_2.png) [@donridley](https://clarionhub.com/u/donridley)\ **Post date:** [March 9, 2026, 11:02am UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/13 "2026-03-09T11:02:19Z")
IsIdentity=True worked for me. That feature is only available in C10 or newer. ---
**Author:** ![Flavio\_Suarez](https://clarionhub.com/user_avatar/clarionhub.com/flavio_suarez/32/1347_2.png) [@Flavio\_Suarez](https://clarionhub.com/u/Flavio_Suarez)\ **Post date:** [March 9, 2026, 11:25am UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/14 "2026-03-09T11:25:32Z")
Hi, Really don’t know… We have this piece of code working for years here at work, with a MS SQL Server on premise… Maybe it’s not the better way, but it works as desired… ---
**Author:** ![Omar\_Squiabro](https://clarionhub.com/user_avatar/clarionhub.com/omar_squiabro/32/4474_2.png) [@Omar\_Squiabro](https://clarionhub.com/u/Omar_Squiabro)\ **Post date:** [March 9, 2026, 11:54am UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/15 "2026-03-09T11:54:38Z")
Don is Available from clarion 6.0 ---
**Author:** ![Rick\_UpperPark](https://clarionhub.com/user_avatar/clarionhub.com/rick_upperpark/32/346_2.png) [@Rick\_UpperPark](https://clarionhub.com/u/Rick_UpperPark)\ **Post date:** [March 9, 2026, 3:13pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/16 "2026-03-09T15:13:24Z")
@@Identity isn’t reliable if there are triggers on your table that might also insert a row in a different table that has an identity column also. @@Identity returns the last identity created on your connection regardless of the table. It is reliable for most databases. ---
**Author:** ![CarlBarnes](https://clarionhub.com/user_avatar/clarionhub.com/carlbarnes/32/1859_2.png) [@CarlBarnes](https://clarionhub.com/u/CarlBarnes)\ **Post date:** [March 9, 2026, 5:41pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/17 "2026-03-09T17:41:47Z")
Users please Quote the part of the message that pertains to your reply so the reader can understand without scrolling back past other replies to read the original. * * * @Flavio_Suarez you replied to @PurpleEdge2214. That message has 2 parts. I’d guess your “We have this piece of code working for years” was referring to this part? > [@PurpleEdge2214](#): > > Also, I don’t think @@Identity is reliable in a multi-user system? ScopeIdentity is better. * * * @Omar_Squiabro you replied “Don is Available from clarion 6.0” but there was a Reply in the middle. You should have quoted Don, or instead of “is” typed “IsIdentity”: Don is Available from clarion 6.0 > [@donridley](#): > > IsIdentity=True worked for me. That feature is only available in C10 or newer. * * * To Quote a post simply select the desired text with your mouse and a Popup menu will offer “Quote | Edit | Copy Quote”. Clikc on “Quote” amd it will be inserted. You can edit the quote, like in Don’s I took out the double space, ---
**Author:** ![Flavio\_Suarez](https://clarionhub.com/user_avatar/clarionhub.com/flavio_suarez/32/1347_2.png) [@Flavio\_Suarez](https://clarionhub.com/u/Flavio_Suarez)\ **Post date:** [March 10, 2026, 1:54pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/18 "2026-03-10T13:54:51Z")
> [@Rick\_UpperPark](#): > > @@Identity isn’t reliable if there are triggers on your table that might also insert a row in a different table that has an identity column also. Good to know! We use no triggers on our internal software’s SQL database (by now)… > [@Rick\_UpperPark](#): > > @@Identity returns the last identity created on your connection regardless of the table. > It is reliable for most databases. This is exactly the way we use it. Right after a TryInsert() inside a transaction block. ---
**Author:** ![FoucheRabie](https://clarionhub.com/letter_avatar_proxy/v4/letter/f/7cd45c/32.png) [@FoucheRabie](https://clarionhub.com/u/FoucheRabie)\ **Post date:** [October 15, 2026, 2:57pm UTC](https://clarionhub.com/t/getting-previous-add-ms-sql-identity-value/8980/19 "2026-10-15T14:57:48Z")
Greetings 🌻 Just sharing, because i know i will be back here. 🤔 These posts where very helpful. Thank you everone! Clarion 11.1.13855 SQL server standard 2019 The following worked for me. Driver Options: /AUTOINCUSESSCOPEIDENTITY=TRUE /MULTIPLEACTIVERESULTSETS=TRUE _ATTRIBUTES_ TAB of the field CUS:IdentityField External Name: IDENTITYFIELD | READONLY _OPTIONS_ TAB of the field CUS:IdentityField ADD+ **TYPE String** Label: IsIdentity Value: TRUE Once saved the Dictionary automatically translates this to... **TYPE Predefined** Label: SoftVelocity.ABC and Clarion.Is Identity Value: TRUE ![image](https://clarionhub-uploads.s3.dualstack.eu-north-1.amazonaws.com/original/2X/d/d997096edfe210d71b2c0fb2645ae9f7e67de181.png)