There is no need to use a temporary table to copy rows from table to table!?
-----Original Message----- From: Cornillon, Matthieu (Consultant) [mailto:[EMAIL PROTECTED] Sent: 28 November 2005 18:55 To: CF-Talk Subject: RE: SQL > Thanks for that, but is there a way to copy a whole row... This is a snippet of code that a friend here at work sent me for creating a whole new row from a whole old row all within SQL. It is Oracle-based, and I have never used it myself, but I am pretty sure that it does what you want. Say you have CUSTOMERS pkCUST CUST_FName CUST_LName CUST_ZIPCode ....and you want to copy the row with pkCUST=37 into a new row of the same table. Then just use this sequence of SQL statements: CREATE TEMPORARY TABLE t SELECT myNewID <!---new primary key---> AS pkCUST, CUST_Fname AS CUST_Fname, CUST_LName AS CUST_LName, CUST_ZIPCode AS CUST_ZIPCode FROM CUSTOMERS WHERE pkCUST = 37; INSERT INTO CUSTOMERS SELECT * FROM t; DROP TABLE t; As I said, I have never used temporary tables, but I wish I had learned about them long ago. I think they are perfect for this sort of thing. I am not sure that in the first statement you need the ColName AS ColName statements; you might be able to just say * to get a copy of all existing rows, but you'd have to test it. Hope this helps, Matthieu ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:225510 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
