SQLite - How to search for value in a column, and if matched, copy data from all colums to a new tab-CodePudding

I have a database of Addresses from all states. I want to separate them by state, 1 table per state. I want to copy all values of all 42 columns from a source table to a destination table with the same columns, if the value of Column 2 is a certain state.

For example, I want to search Column 2 for IN (Indiana), and if IN is in column 2, copy data from all columns to a separate table named Indiana.

CodePudding user response：

Here is in steps what to do:

create your destination databases. It's important that these contain the same table structure as the source table. So basically, in pseudocode, do this:

      for each value in data column:
           execute this with sqlite:
              sqlite3 stateX.db "create table adresses (id, state, zip, road...)"

now open your source file with sqlite: sqlite3.exe mainfile.db
you can now export your data. Example: IN for Indiana. Comments are added in SQL.

/* do this with Indiana */
ATTACH 'stateIN.db' as other;

/* insert data, select 'IN' from field 'thestate' */
INSERT INTO other.adresses 
   SELECT * FROM address WHERE thestate='IN';

/* done, detach the other database */
DETACH other;

repeat same for other values

tip: you can create SQL files automatically for every unique value in your source column (this is a different topic).

Each of these SQL files could look like:

/* CA.sql  file */
attach 'CA.db' as oth;    /* for california */
create table oth.address (c1,c2,....);   /* edit as needed */ 
insert into oth.abc select * from address; 
detach oth;

and the run using

sqlite3.exe addresses.db < ca.sql