Re: How to Move data between tables (newbie question)

A copy of this was sent to hedo_ii_rat_at_my-deja.com (if that email address didn't require changing) On Thu, 24 Jun 1999 14:26:45 GMT, you wrote:

>I have just moved out of Transact SQL on Sybase
>to PL/SQL on Oracle. I am having a difficult
>time finding a simple way to copy the data from
>one table to another. In Sybase, we had a SELECT
>INTO statement that let us pick which columns
>from one table we wanted to copy to another. I
>know SELECT ... INTO ... in Oracle is not the
>same, however, isn't there an easier way to do
>this than declaring a hundred variables,
>selecting into them, then inserting them into
>another table on a row by row basis with a loop?
>

create table T
as
select * from another_T;

is similar to select into in sybase.

insert into T select * from another_T;

works in both databases if T already exists.

>And under the wandering thoughts category...
>
>Have you ever wondered... if SQL is supposed non
>database specific query language, why does every
>database (Sybase, Oracle, SQLServer, Informix)
>have it's own version of SQL? Usually only a few
>simple selects will work on all database. Makes
>the crossover a very frustrating process!
>

If you stick with ANSI SQL/92 Entry level SQL, you will in most cases be OK. Given that, you will find that create table as select and insert into are both the standard.

Its not that we have our own versions of SQL, its that we all have extensions that go above and beyond. The use of these extensions are optional but in the real work, if you want performance, you will use them.

>Thanks for the help,
>
>Dan Nobra
>dnobra_at_txpetrochem.com
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jun 24 1999 - 09:44:59 CDT