CTAS question

All,

I have the following requirement:

Table A exists.
Table B should be a replica of Table A (structurally).

The catch is this: Table B will have a Primary Key column and this column should be the first column, followed by all columns from Table A.

Is the above possible through SQL? If so, please provide some hints.
If not, please let me know ASAP, so that I can start scripting.

Thanks!
Suhas

if it has an extra column then it is not a replica

why not just do an after insert update or delete trigger?

I dont want to create an exact replica, per se.
The requirement is: Table B has one column of its own plus all columns of Table A.
Is it possible to achieve this through SQL? The one column of Table B should be placed in position #1.
And I dont care about data, so question of triggers arise.

Shall we understand you want to create an empty table which has pretty much the same structure of an existing table?

Yes. You can do that through SQL

I would use a merge statement. You would not have to drop table b, its constraints or indexes. You would not need to completely recreate the table either.

http://download.oracle.com/docs/cd/B...6.htm#i2081218

Easy does it:

Code:

---

CREATE TABLE TableB
AS
    SELECT ROWNUM TableB_pk, TableA.* FROM TableA;

---

:p