Can I use SQL to create multiple tables from a very large table using a particular series of field variables as...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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:
create 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:
insert 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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.