I found that so many query statements are different between in Microsoft SQL and Oracle. To copy two table in the same database with Microsoft SQL, I normally use SELECT INTO statement as follow.

view plain print about
1<!--- It's for all data fields --->
2<cfquery name="qryCopy" datasource="myDSN">
3    SELECT *
4    INTO tblPerson_Backup
5    FROM tblPerson
6</cfquery>
7
8<!--- For specific data field --->
9<cfquery name="qryCopy" datasource="myDSN">
10    SELECT NAME, DOB, SEX
11    INTO tblPerson_Backup
12    FROM tblPerson
13</cfquery>

In Oracle, I cannot do so. That's why I gotta keep learning Oracle tutorials and googling how to copy tables in Oracle. Fortunately, I found that there is Create table command also support us to make clone table in Oracle. How to do is as follow

view plain print about
1<!--- It's for all data fields --->
2<cfquery name="qryCopy" datasource="myDSN">
3    CREATE TABLE tblPerson_Backup AS
4    SELECT *
5    FROM nh_master_item
6</cfquery>
7
8<!--- For specific data field --->
9<cfquery name="qryCopy" datasource="myDSN">
10    CREATE TABLE tblPerson_Backup AS
11    SELECT NAME, DOB, SEX
12    FROM nh_master_item
13</cfquery>

This entry was posted on December 30, 2010 at 7:44 PM and has received 1525 views. There are currently 0 comments. Print this entry.