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
  • RE: SQL RADEMAKERS Tanguy
    • RE: SQL Robertson-Ravo, Neil (RX)
    • RE: SQL Cornillon, Matthieu \(Consultant\)

Reply via email to