Back in January of last year i wrote a post on how to write a query on how to write a MS SQL query that would output the ID of the row that you are deleting without having to write 2 seperate queries. Now this does not only work on delete, but it also works on updates and inserts.
Before SQL 2005 you would need to use @@IDENTITY,SCOPE_IDENTITY, or IDENT_CURRENT to select out your inserted ID. The problem that can plague some of these is when you have several queries on the same page that are performing this same operation, you can have adverse results.
From SQL 2005 to current you can now use OUTPUT on your statement to output a Deleted, Updated, or Inserted id without running a seperate query before the action to determine the ID. I have seen many developers fall prey to running 2 queries to determine the ID being changed and then changing it. The problem with this is that you force your application to prepare and create 2 seperate threads for a transaction that you can do with 1 statement. We all like to trim our code to be efficient, so SQL has helped with this by allowing you to OUTPUT your modified rows id in the same statement as the one doing the modifications.
So a few days ago i thought, well I know how to do that but what if I wanted to insert a row in 1 table and then use that id to update a row in another table. Not only how to do that but how do you do all that within 1 statement so as to keep my ColdFusion application from having to prepare and send 2 separate statements. so here is the example.
<cfquery name="qinsertRec" datasource="#datasource#">
DECLARE @retId TABLE(id int);
INSERT INTO testtable1
(source,
dest,
TransDate,
username,
backupKey,
filename,
[action])
OUTPUT INSERTED.id INTO @retId
VALUES
(<cfqueryparam cfsqltype="cf_sql_varchar" value="#copyFrom#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#copyto#">,
<cfqueryparam cfsqltype="cf_sql_date" value="#dateTime#">,
<cfqueryparam cfsqltype="cf_sql_integer" value="1234">,
<cfqueryparam cfsqltype="cf_sql_bigint" value="14">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="test.txt">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="Restore">);
UPDATE testtable2
SET trans_id_rest = (SELECT id
FROM @retId),
status = 'Restored'
WHERE id = <cfqueryparam cfsqltype="cf_sql_integer" value="14">;
</cfquery>
DECLARE
First we declare a temp table to output our ID into.
INSERT
Then we run our insert statement, remember to put your output between your column definitions and your values. Now instead of just writing OUTPUT INSERTED.id, we are going to put OUTPUT INSERTED.id INTO @retId. This will insert the id into our temp table so that we can select it back out when we run our UPDATE statement. Of course always remember to use your cfqueryparam’s to prevent for injections of people that want to do malicious things to your site.
UPDATE
Now we can do our update to the other table and select out the id from the declared temp table.
MJDDesign Concepts LLC. 
You must be logged in to post a comment.