CODEWITHSUNDEEP
sql
Brian V.
Brian V.

Reputation: 37

SQL query: have results into a table named the results name

I have a very large database I would like to split up into tables. I would like to make it so when I run a distinct, it will make a table for every distinct name. The name of the table will be the data in one of the fields.

EX:

A    ---------   Data 1
A    ---------   Data 2
B    ---------   Data 3
B    ---------   Data 4

would result in 2 tables, 1 named A and another named B. Then the entire row of data would be copied into that field.

select distinct [name] from [maintable]
-make table for each name
-select [name] from [maintable]
-copy into table name
-drop row from [maintable]

Any help would be great!

Upvotes: 0

Views: 120

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I would advise you against this.

One solution is to create indexes, so you can access the data quickly. If you have only a handful of names, though, this might not be particularly effective because the index values would have select almost all records.

Another solution is something called partitioning. The exact mechanism differs from database to database, but the underlying idea is the same. Different portions of the table (as defined by name in your case) would be stored in different places. When a query is looking only for values for a particular name, only that data gets read.

Generally, it is bad design to have multiple tables with exactly the same data columns. Here are some reasons:

  • Adding a column, changing a type, or adding an index has to be done times instead of one time.
  • It is very hard to enforce a primary key constraint on a column across the tables -- you lose the primary key.
  • Queries that touch more than one name become much more complicated.
  • Insertions and updates are more complex, because you have to first identify the right table. This often results in overuse of dynamic SQL for otherwise basic operations.

Although there may be some simplifications (security comes to mind), most databases have other mechanisms that are superior to splitting the data into separate tables.

Upvotes: 1

Dany Y
Dany Y

Reputation: 7031

what you want is 

CREATE TABLE new_table
  AS (SELECT .... //the data that you want in this table);

Upvotes: 0

Related Questions