CODEWITHSUNDEEP
sqloracle-database
Tchypp
Tchypp

Reputation: 1105

Can I use a type of an existing table's column when creating a table?

I am curious if I could use the type of an existing table's column when I create another column. Just as using a data type like varchar, I would like to have my column the same type as a column in another table.

I am imagining something like CREATE TABLE FIRST (id NUMBER(5), name VARCHAR(25))

and then

CREATE TABLE SECOND (id NUMBER(6), value FIRST.NAME%TYPE)

and the type of the VALUE column would be VARCHAR(25)

I see this as a generic SQL question, though I am using Oracle.

Upvotes: 4

Views: 4291

Answers (3)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95572

In standard SQL, you'd write a CREATE DOMAIN statement.

CREATE DOMAIN my_name_type VARCHAR(25);

But I don't think Oracle supports CREATE DOMAIN, so you I think you need to create an object instead.

create type my_name_type as object
( my_name_col varchar2(25));

In either case, you'd use it directly in creating a table.

create table test (
    user_name my_name_type
);

I recall that the syntax for INSERT statements is a little weird; check the docs for that.

Upvotes: 1

JNK
JNK

Reputation: 65157

You could do this in SQL Server by using SELECT INTO:

SELECT TOP 0 CAST(0 as int) as 'id', Field
INTO NewTable
FROM OtherTable

Upvotes: -1

vc 74
vc 74

Reputation: 38179

You can do the following:

  CREATE TABLE SECOND AS (
    SELECT ID, NAME AS VALUE 
    FROM FIRST
    WHERE 1 = 2
  );

If think the %type syntax is a plsql thing only

Upvotes: 2

Related Questions