Using Identity insert
I was looking at Todd Sharp's getGeneratedKey function on UDFLib, which returns the identity (auto generated primary key) when you insert data into a database. It works by making use of the result attribute of cfquery, that was introduced in ColdFusion 8.
This lead me to think about what ColdFusion was doing under the hood and how you could do it with older versions of ColdFusion.
Consider a table called myTable, that has two columns: myprimarykey, which is an identity field (auto incrementing primary key) and a second field called myfield. In Microsoft SQL Server 2000 and higher you have 4 options to get the value of myprimarykey when you insert a record.
Max(identity) from myTable
Returns the highest id from the given table. This is not reliable as an insert may have occurred between your insert and select queries. Even with the use of a lock, you don't know if another process (such as a trigger, 3rd party software or even a dba!) has inserted a record.
Example:
<cflock type="application" timeout="10">
<cfquery name="qryInsert" datasource="foo">
INSERT INTO [myTable] (
myfield
)
VALUES (
cfqueryparam value="#Rand()#" cfsqltype="CF_SQL_FLOAT" />
)
</cfquery
<cfquery name="qryGetIdentity" datasource="foo">
SELECT MAX(myprimarykey) as InsertIdentity
FROM [myTable]
</cfquery>
</cflock>
<cfoutput>#qryGetIdentity.InsertIdentity#</cfoutput>
@@IDENTITY
Returns the last identity created by your connection. If you have triggers in your database then this is unreliable, as it could return the insert value created by the trigger, even if it is on a different table.
Example:
<cfquery name="qryInsert" datasource="foo">
INSERT INTO [myTable] (
myfield
)
VALUES (
cfqueryparam value="#Rand()#" cfsqltype="CF_SQL_FLOAT" />
)
SELECT InsertIdentity = @@IDENTITY
</cfquery
<cfoutput>#qryInsert.InsertIdentity#</cfoutput>
IDENT_CURRENT('tablename')
Returns the last identity for the given table. Be careful as this may not be the identity that was created by your insert.
Example:
<cfquery name="qryInsert" datasource="foo">
INSERT INTO [myTable] (
myfield
)
VALUES (
cfqueryparam value="#Rand()#" cfsqltype="CF_SQL_FLOAT" />
)
SELECT InsertIdentity = IDENT_CURRENT('myTable')
</cfquery
<cfoutput>#qryInsert.InsertIdentity#</cfoutput>
SCOPE_IDENTITY()
Returns the last identity created by within the current scope. This ignores records created by triggers and user defined functions so is the safest option.
Example:
<cfquery name="qryInsert" datasource="foo">
INSERT INTO [myTable] (
myfield
)
VALUES (
cfqueryparam value="#Rand()#" cfsqltype="CF_SQL_FLOAT" />
)
SELECT InsertIdentity = SCOPE_IDENTITY()
</cfquery
<cfoutput>#qryInsert.InsertIdentity#</cfoutput>
Conclusions
My advice would be to use Todd Sharp's method if you have ColdFusion 8, otherwise use SCOPE_IDENTITY(). In most cases the other methods will work fine, but they only need to go wrong once, and you'll have a major headache!
- Posted in:
- SQL
- ColdFusion
