At the moment I have one large SQL table. Let's say it looks like this:
Table: allData
County Census Tract Population Name
001 xxxxxx 4328 County1
001 yyyyyy 4729 County1
002 zzzzzz 5629 County2
003 aaaaaa 3947 County3
What I want is an individual table for each county. So I would have:
Table: County1
County Census Tract Population Name
001 xxxxxx 4328 County1
001 yyyyyy 4729 County1
Table: County2
County Census Tract Population Name
002 zzzzzz 5629 County2
Table: County3
County Census Tract Population Name
003 aaaaaa 3947 County3
Thanks.
If your use case requires a separate table for each County you can use the following SQL to generate create table
statements
select 'create table '+Name+' as select * from allData where name = '''+Name+''';'
from allData
group by name
This SQL will generate insert
statements
select 'insert into '+Name+' select * from allData where name = '''+Name+''';'
from allData
group by name
You can run each of these above and copy paste the result into your SQL client to create and populate the tables
If you need to specify schema names add it right before Name
like
select 'create table <schema.>'+Name+' as select * from allData where name = '''+Name+''';'
I have used the following SQL to test the same
with allData as (
select '001' County, 'xxxxxx' Census_Tract, 4328 Population, 'County1' Name union all
select '001', 'yyyyyy', 4729, 'County1' union all
select '002', 'zzzzzz', 5629, 'County2' union all
select '003', 'aaaaaa', 3947, 'County3' )
select 'create table '+Name+' as select * from allData where name = '''+Name+''';'
group by name;
with allData as (
select '001' County, 'xxxxxx' Census_Tract, 4328 Population, 'County1' Name union all
select '001', 'yyyyyy', 4729, 'County1' union all
select '002', 'zzzzzz', 5629, 'County2' union all
select '003', 'aaaaaa', 3947, 'County3' )
select 'insert into '+Name+' select * from allData where name = '''+Name+''';'
from allData
group by name;