Problem solve Get help with specific problems with your technologies, process and projects.


Can I use SQL to create multiple tables from a very large table using a particular series of field variables as...

the newly created field names? In other words, can I generate lots of tables from one big one?

Yes. There are two approaches: use SELECT/INTO to create the new table "on the fly" or use CREATE to define the new table and then INSERT/SELECT to populate it from the large table.

Here's an example of SELECT/INTO syntax:

select deptno
     , 57 as projectmgrid
     , division as divno
     , cast(section as char(4)) as sectno
  into newtable1
  from bigtable
 where foo = 'bar'

The only thing you can do to define the characteristics of the new table are to assign or change the name of the column (e.g. projectmgrid, divno) and/or the datatype using CAST (e.g. sectno). Note that not every database system supports SELECT/INTO. To do more, you would have to use CREATE syntax first:

 table newtable2
     ( id           integer  not null identity
     , deptno       integer  not null
     , projectmgrid smallint not null default 57
     , divno        char(3)      null
     , sectno       char(4)  not null

Note the identity field (syntax specific to SQL Server), the assignment of NULL and NOT NULL, and the use of a default value for the projectmgrid column. You cannot declare these attributes with SELECT/INTO. You would then populate this table with INSERT/SELECT:

  into newtable2
     ( deptno
     , divno 
     , sectno
select deptno
     , division
     , cast(section as char(4))     
  from bigtable
 where foo = 'bar'

To create multiple tables from the big table, just repeat the SELECT/INTO or INSERT/SELECT approach as often as required.

This was last published in December 2003

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.