That works great! 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.
<cfquery datasource="#application.dsn#" name="insertTicket"> 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 </cfquery> > -----Original Message----- > From: Leigh [mailto:cfsearch...@yahoo.com] > Sent: Tuesday, December 15, 2009 1:40 PM > To: cf-talk > Subject: Re: copy from table to table > > > > Is there any easy way to take a row of data from one table > > and copy it to another? The tables have the exact same > > columns, they just have different table names. > > In most databases you can do an INSERT _without_ listing the column names. > Though if the table an auto-number column, you may need to enable special > settings so you can explicitly insert auto number values. Such as ms sql's > SET IDENTITY INSERT command. > > INSERT INTO TableName SELECT * FROM SomeTable > > Having said that, I prefer to list out the column names to avoid mistakes > caused by tables with the same column names ... but different orders. If > you are using ms sql/my sql the INFORMATION_SCHEMA views are great for > generating the column lists quickly. > > SELECT COLUMN_NAME +',' FROM INFORMATION_SCHEMA.COLUMNS WHERE ... > > -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:329163 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
