Geek asks and answers

Oracle Filling a backup table from a primary table

In the program that I have currently assigned, there is a requirement that I copy the contents of the table to the backup table before actual processing.

While reviewing the code, an employee noticed that

INSERT INTO BACKUP_TABLE
SELECT *
FROM PRIMARY_TABLE

unreasonably risky, since tables can have different columns and different column orders.

I also cannot create / delete / rename tables. ~ ~ Sigh

The columns in the table are expected to change, so just hard coding the column names is not really the solution I'm looking for.

I'm looking for ideas on a reasonable, non-risky way to get this job done.

+3
source share
5 answers

? , ?

- ///. , , , - , -

create table backup_table as select * from primary_table;

, ,

insert into backup_table (<list of columns>) select <list of columns> from primary_table;

, SQL , . , backup_table primary_table.

, , backup_table , primary_table backup_table.

+7

? , . , , , . - , , DELETE FROM TABLE;.

+2

, ?

INSERT INTO backup_table( col1, col2, col3, ... colN )
  SELECT col1, col2, col3, ..., colN
    FROM primary_table

, , , , , , , , .

+1

, . , , :

INSERT INTO BACKUP_TABLE
SELECT *
FROM PRIMARY_TABLE

, , . :

INSERT INTO BACKUP_TABLE (<list of columns>) 
SELECT <list of columns> 
FROM PRIMARY_TABLE

, , , , , , PK , . , , . , , .

, -, , , . , , .

, , , .

+1

- :

CREATE TABLE secondary_table AS SELECT * FROM primary_table;

, . :

CREATE TABLE secondary_table AS SELECT * FROM primary_table LIMIT 1;
INSERT INTO secondary_table SELECT * FROM primary_table;

Edit:

, : . , , . , , /.

However, if you are using a backup table, I think it is very important, it exactly matches the original.

0
source

More articles:


All Articles