Cool... that worked. I will read up on this NOCOUNT and "output clause" so I understand what is going on.
Thanks again! Chad > -----Original Message----- > From: Leigh [mailto:[email protected]] > Sent: Tuesday, December 15, 2009 3:15 PM > To: cf-talk > Subject: RE: copy from table to table > > > > How would I format a SCOPE_IDENTITY() around this type of > > query? I tried this and I don't get the ID back > > out. The SQL does not error but when I output > > #insertTicket.JobTicketID# CF says it is not defined. > > Most likely the row count from the insert is probably throwing off > cfquery. You can either wrap the query in SET NOCOUNT > > SET NOCOUNT ON; > INSERT INTO JobTicket (JobNum, DateCreated) > SELECT JobNum, #NOW()# > FROM JobTicket > WHERE JobTicketID = <cfqueryparam > cfsqltype="cf_sql_integer" value="#URL.JobTicketID#">; > SELECT SCOPE_IDENTITY() AS JobTicketID FROM JobTicket > SET NOCOUNT OFF; > > Another option is to use an output clause: > http://cfsearching.blogspot.com/2009/04/ms-sql-2005-underused-output- > clause.html > > -Leigh > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329168 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
