Insert into multiple tables; grab id from first

[MarianneValentine]

I apologize if this has been answered elsewhere, but I tried searching and got no results.

I'm trying to insert the contents of one form into two tables. After inserting some fields into one table, I want to grab the key/id from the first table into the second table with the other fields.

I've tried a few different things, including trying to use SCOPE_IDENTITY(), but just kept getting lots of errors.
The current version of my code works perfectly, *except for the fact that it doesn't insert the ID*!

Can anyone tell me what I'm doing wrong? or offer another way of doing this?
Thanks for your help, as usual.

[Mi-ul-vmijiI]

I'm hoping to see a better answer soon but I've been using

<cfset bookerCode = #ceiling(rand()*1000)#>
<query name="createID">
insert into t1 (bookerCode = #bookerCode#)
</query

<query name="getID">
select tableID from t1 where bookerCode = #bookerCode#
</query>

<query name="insertT1">
insert into t1 (myfields = #myvalues#, bookerCode = '0') where tableID = #getID.tableID#

</query>

<query name="insertT2">
insert into t2 (myfields = #myvalues#, bookerCode = '0') where tableID = #getID.tableID#
</query>

[-__cfSearching__-]

MarianneStone wrote:
> The current version of my code works perfectly, *except for the fact that it doesn't insert > the ID*!
> Can anyone tell me what I'm doing wrong?

The EmployeeID is not inserted because the getMyID query cannot find the newly inserted record. When using a UUID to identify the new records ..

<cfquery name="qAddDeptHead" datasource="#db#" result="result">
INSERT INTO Employee(... , EmpUUID)
VALUES ( ... , '#CreateUUID()#')
</cfquery>

You have to use that _same_ UUID to find the record, _not_ create a new one.

<cfquery name="getMyID" datasource="#db#">
SELECT EmployeeID, EmpUUID
FROM Employee
WHERE Employee.EmpUUID='#UseTheSameUUIDFromTheFirstQuery#'
</cfquery>

As an aside, you could probably do this in two queries not three. Just drop the second SELECT query and use an INSERT INTO/SELECT .. FROM Table statement instead. Plus you should also consider using cfqueryparam for your query parameters. It has many benefits.
http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_18.html#1102474

> Can anyone ... offer another way of doing this?

There are a few options. For ColdFusion 8, you could use the result attribute. After running the INSERT you can grab the new EmployeeID by using #yourResult.IDENTITYCOL#

<cfquery name="qAddDeptHead" datasource="#db#" result="yourResult">
INSERT INTO Employee ( Columns here )
VALUES (... values here ...)
</cfquery>

For earlier versions, you can either use SCOPE_IDENTITY() or a UUID value - as you are currently doing now.

> I've tried a few different things, including trying to use SCOPE_IDENTITY(),
> but just kept getting lots of errors.

If you use SCOPE_IDENTITY() turn off the rowcount, or the query variable "qAddDeptHead" may be undefined when you try and use it.

<cfquery name="qAddDeptHead" datasource="#db#" result="yourResult">
SET NOCOUNT ON;
INSERT INTO Employee ( Columns here )
VALUES (... values here ...)
SELECT EmployeeID AS NewEmployeeID
SET NOCOUNT OFF;
</cfquery>

<cfquery name="qNewEmpPosition" datasource="#db#">
INSERT INTO EmployeePositions(EmployeeID, .... )
VALUES ( #qAddDeptHead.NewEmployeeID#, ....)
</cfquery>

[-__cfSearching__-]

.. If you use the SCOPE_IDENTITY() method, both INSERT's should be wrapped in a transaction to ensure data consistency.

[MarianneValentine · Accepted solution]

Okay! I got it working. Thanks very much! Here's what I ended up doing (I'm just posting the code for the INSERTs). I will play around with cfqueryparam some later - thanks so much for the valuable info!

[MarianneValentine]

Thank you both very kindly! I haven't had a chance yet to try these but when I do I will post back with the result.