使用 Oracle SELECT INTO

2021-12-05 00:00:00 sql oracle ora-00905

我正在尝试使用 Oracle 执行 SELECT INTO.我的查询是:

I'm trying to do a SELECT INTO using Oracle. My query is:

SELECT * INTO new_table FROM old_table;

但我收到以下错误:

SQL Error: ORA-00905: missing keyword
00905. 00000 -  "missing keyword"

知道有什么问题吗?

上面的标准行为应该是我原先认为的:然而,Oracle 用他们自己的 SQL 方言完全不同地实现了它插入 Oracle 文档...选择

The Standard behavior of the above should be as I originally thought: However Oracle implemented it totally differently in their own dialect of SQL Oracle Docs on Insert ... Select

推荐答案

如果 NEW_TABLE 已经存在,则 ...

If NEW_TABLE already exists then ...

insert into new_table 
select * from old_table
/

如果你想根据 OLD_TABLE 中的记录创建 NEW_TABLE ...

If you want to create NEW_TABLE based on the records in OLD_TABLE ...

create table new_table as 
select * from old_table
/

如果目的是创建一个新的但为空的表,则使用 WHERE 子句,其条件永远不会为真:

If the purpose is to create a new but empty table then use a WHERE clause with a condition which can never be true:

create table new_table as 
select * from old_table
where 1 = 2
/

请记住,CREATE TABLE ... AS SELECT 仅创建一个与源表具有相同投影的表.新表没有原始表可能具有的任何约束、触发器或索引.那些仍然必须手动添加(如果需要).

Remember that CREATE TABLE ... AS SELECT creates only a table with the same projection as the source table. The new table does not have any constraints, triggers or indexes which the original table might have. Those still have to be added manually (if they are required).

相关文章