CODEWITHSUNDEEP
javaoracle-database
Anita
Anita

Reputation: 185

Equivalent statement to SQL Server's SELECT INTO in Oracle

I am a newbie to oracle. I have a requirement where i have to insert the values of some table to a temporary table. For instance, In MSSQL I used to use the query something like below.

Note: There can be much more complicated select query result, inserted to the temp table. i.e, actualtable in the below example can be any complicated query. Here i have just mentioned a simple example.

SELECT columna, columnb... INTO temptable FROM actualtable where columna=? and columnb=? and ..;

After the construction of above select query, i am doing data binding as well by preparing statement in Java.

In oracle I cannot do as above, I have to use as below

create table temptable as select columna, columnb... from actualtable where columna=? and columnb=? and ..;

After this I cannot do data binding. If I do, i get the exception - ORA-01027: bind variables not allowed for data definition operations. As its known I cannot do data binding for create table, create view etc.

What else can i do? I cannot create temp table before in hand. Because, i will not know the description of all the columns of temp table. Kindly help. Thanks in advance!

Upvotes: 0

Views: 3143

Answers (1)

Chris
Chris

Reputation: 4231

You can do this in two steps. First create the table with 0 rows by specifying a where condition that is always false:

CREATE TABLE temptable AS SELECT * FROM actualtable WHERE 1 = 0;

Then use insert into .. select ..., which is a DML statement, so you can use bind variables:

INSERT INTO temptable SELECT * FROM actualtable WHERE where columna=? and columnb=? and ..;

Upvotes: 3

Related Questions