OraFAQ Forum: SQL & PL/SQL » How can I model a column in a new table after a column in an existing table?

Home » SQL & PL/SQL » SQL & PL/SQL » How can I model a column in a new table after a column in an existing table?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How can I model a column in a new table after a column in an existing table? [message #682641]

Wed, 04 November 2020 12:45

Darth Waiter
Messages: 41
Registered: October 2020

Member

Hi all:

Most of my background is on MS SQL, and I know very basics of Oracle. When I create CRUD stored procedures, I use the underlying table column's type for parameters all the time.

v_col1 IN schema.table.column%TYPE

Can I do the same for some but not for all columns, when I create tables that depend on an existing table? For instance, if I have

create table a(a varchar2(10));

How can I

create table b(b ...%type);

where type would be derived from a.a and be the same varchar2(10) but without declaring it explicitly?

Thank you!
Al

[Updated on: Wed, 04 November 2020 12:49]

Report message to a moderator

Re: How can I model a column in a new table after a column in an existing table? [message #682642 is a reply to message #682641]

Wed, 04 November 2020 13:13

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

create table b (b,...) as select a,... from a where 1=2;

or, simpler, if the columns have same names:

create table b as select * from a where 1=2;

Report message to a moderator

Re: How can I model a column in a new table after a column in an existing table? [message #682643 is a reply to message #682642]

Wed, 04 November 2020 13:30

Darth Waiter
Messages: 41
Registered: October 2020

Member

This models the whole table after the original one, but I am only looking to model some columns.
I understand that I can use literals for the rest but that will require a cast on each of them which outweighs the benefit of modeling some columns after the original.

Report message to a moderator

Re: How can I model a column in a new table after a column in an existing table? [message #682644 is a reply to message #682643]

Wed, 04 November 2020 13:59

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If your "model" has not the same columns or column definitions then, anyway, you have to express what you want in the SELECT part and if the types do not match you indeed have to specify/cast the type (but how is this a model then?).

In short, it is:

create table b as select <what I want in b> from a where 1=2;

Maybe I don't understand what you want, in this case I think you should specify in an example.

Report message to a moderator

Re: How can I model a column in a new table after a column in an existing table? [message #682658 is a reply to message #682644]

Thu, 05 November 2020 10:02

EdStevens
Messages: 1375
Registered: September 2013

Senior Member

If the two tables don't match, then you are going to have to do a lot of hand-coding anyway. At that point, you are already looking at the 'source' table, so you already know the data types. I don't see any real gain in trying to 'model' the data types of specific, selected columns.

Report message to a moderator

Previous Topic:	A regular user cannot create Varchar2(32767)
Next Topic:	How can I execute multiple SQL statements in one batch?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 31 02:07:43 CDT 2023