Home » SQL & PL/SQL » SQL & PL/SQL » Does this exist in sql
Does this exist in sql [message #248079] Thu, 28 June 2007 00:39 Go to next message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Hi gurus,

Does something like this exist in Oracle SQL.

SELECT * INTO Persons_backup
FROM Persons


I found this particular thing

Quote:
http://www.w3schools.com/sql/sql_select_into.asp


I tried this in SQL*plus,but it shows the following error.

SELECT * INTO emp1 from emp
              *
ERROR at line 1:
ORA-00905: missing keyword


Please advice.

Thanks in advance...

Srini...
Re: Does this exist in sql [message #248086 is a reply to message #248079] Thu, 28 June 2007 01:00 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Simply avoid the INTO clause to make it SQL statement.
Or call it in the PL/SQL anonymous block.
Hard to tell as I do not know what do you want to do with persons_backup variable.
Also SELECT * is not a bad practice, you should list all columns you want.
Re: Does this exist in sql [message #248087 is a reply to message #248079] Thu, 28 June 2007 01:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is not Oracle syntax.
In Oracle, select into is used in plsql, to store the result of a query in a variable.
The described functionality would be done in Oracle by using a
create table <newtab> as select ....
Re: Does this exist in sql [message #248088 is a reply to message #248079] Thu, 28 June 2007 01:04 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

you will select into in PLSQL constructs.

A simple construct using select into can be,
declare
v1 emp.ename%type;
v2 emp.sal%type;
Begin
select ename,sal into v1,v2 from emp where ename= 'SCOTT';
dbms_output.put_line(v1||' '||v2);
end;
Re: Does this exist in sql [message #248091 is a reply to message #248079] Thu, 28 June 2007 01:05 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
You have 2 choices with Oracle:
1. The backup table does NOT exists, so you create it and backup your data (Frank already mentioned it):
CREATE TABLE person_backup  NOLOGGING AS SELECT * FROM person;


2. The empty backup table already defined.
INSERT INTO person_backup SELECT * FROM person;


HTH.
Michael

Re: Does this exist in sql [message #248352 is a reply to message #248091] Thu, 28 June 2007 13:17 Go to previous message
srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA
Member
Thanks a lot everyone.Just got confused with the select statements mentioned in the W3schools website.

http://www.w3schools.com/sql/sql_select_into.asp



Regards
Srini...
Previous Topic: help with clob output
Next Topic: getting the values from a cursor on different tables
Goto Forum:
  


Current Time: Thu Dec 08 10:13:35 CST 2016

Total time taken to generate the page: 0.11391 seconds