SELECT/INTO and INSERT/SELECT
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:
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.