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.
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.
Easy does it:
CREATE TABLE TableB
SELECT ROWNUM TableB_pk, TableA.* FROM TableA;