oracle 将备份表数据还原为原始表 SQL

Revert backup table data to original table SQL

提问by Aniket Thakur

I have created a backup for my country table.

我已经为我的国家/地区表创建了一个备份。

create table country_bkp as select * from country;

What SQL should I use to restore the countrytable to it's original state? I can do

我应该使用什么 SQL 将country表恢复到原始状态？我可以

insert into country select * from country_bkp;

but it will just have duplicate entries and probably fail as primary key would be same .

但它只会有重复的条目，并且可能会因为主键相同而失败。

Is there an SQL command to merge data back?

是否有 SQL 命令来合并数据？

Last alternative would be

最后一个选择是

DROP TABLE country;
create table country as select * from country_bkp;

but I want to avoid this as all the grants/permissionswould get lost by this.

但我想避免这种情况，因为所有这些grants/permissions都会因此而迷失。

Other cleaner way would be

其他更清洁的方法是

delete from country ;
insert into country select * from country_bkp;

But I am looking for more of a merge approach without having to clear data from original table.

但我正在寻找更多的合并方法，而不必清除原始表中的数据。

回答by Mureinik

Instead of dropping the table, which, as you noted, would lose all the permission defitions, you could truncateit to just remove all the data, and then insert-select the old data:

而不是删除表，正如您所指出的，这会丢失所有权限定义，您可以truncate只删除所有数据，然后插入选择旧数据：

TRUNCATE TABLE country;
INSERT INTO country SELECT * FROM county_bkp;

回答by m.rahul

Only One Solution to Recover Data from Backup table is Rename Original table with random name and than rename Backup table with Original Table name in case if Identity Insert is ON for Original Table.
for example
Original Table - Invoice
Back Up Table - Invoice_back

从备份表中恢复数据的唯一一种解决方案是使用随机名称重命名原始表，然后在原始表的标识插入为 ON 的情况下使用原始表名称重命名备份表。
例如
原始表 - 发票
备份表 - Invoice_back

Now Rename these tables :

现在重命名这些表：

Original Table - Invoice_xxx
Back Up Table - Invoice

原始表 - Invoice_xxx
备份表 - 发票

回答by Safi Ullah

In my case, INSERT INTO country SELECT * FROM county_bkp;didnt work because:

就我而言，INSERT INTO country SELECT * FROM county_bkp;没有用，因为：

• It wouldnt let me insert in Primary Key column due to indentity_insertbeing off by default.
• My table had TimeStampcolumns.

• 由于indentity_insert默认情况下关闭，它不会让我插入主键列 。
• 我的桌子有TimeStamp列。

In that case:

在这种情况下：

• allow identity_insertin the OriginalTable
• insert query in which you mention all the columns of OriginalTable(Excluding TimeStampColumns) and in Valuesselect all columns from BackupTable(Excluding TimeStampColumns)
• restrict identity_insert in the OriginalTable at the end.

• 允许identity_insert在OriginalTable
• 插入查询，您在其中提及OriginalTable（排除TimeStamp列）的Values所有列并从中选择所有列BackupTable（排除TimeStamp列）
• 最后限制在 OriginalTable 中的 identity_insert。

EXAMPLE:

例子：

Set Identity_insert OriginalTable ON
    insert into OriginalTable (a,b,c,d,e, ....) --[Exclude TimeStamp Columns here]
    Select a,b,c,d,e, .... from BackupTable --[Exclude TimeStamp Columns here]

    Set Identity_insert OriginalTable Off