1) given that you have to list all the columns in your "create temporary table as select..." statement, you might as well just do
"insert into customers (select myNewID, pkCUST, CUST_Fname, CUST_LName, CUST_ZIPCode from customers where pkCUST = 37);" 2) "myNewID" is most probably a sequence, so it should be something like "myNewID.nextval" 3) no, you cannot just use * to get all the columns, because then you'd get the existing ID as well, which you don't want. Regards, /t >-----Original Message----- >Subject: SQL >From: "Cornillon, Matthieu \(Consultant\)" ><[EMAIL PROTECTED]> >Date: Mon, 28 Nov 2005 13:54:54 -0500 >Thread: >http://www.houseoffusion.com/cf_lists/index.cfm/method=messages >&threadid=43478&forumid=4#225445 > >> 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:225509 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
