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.