0

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.

SMan
  • 3
  • 2
  • I think what you want is [explained here](https://stackoverflow.com/questions/29220677/sqlite-easiest-way-to-copy-a-table-from-one-database-to-another). You just need to use `SELECT * from addresstable where state_column="IN"` (obviously change to fit your table. – MyICQ Jun 08 '22 at 21:32
  • Thanks for the answer @MyICQ. I gave it a shot and got an error. I created a database in the same dir as the main database: ATTACH DATABASE 'Indiana.db' AS other; INSERT INTO other.tbl SELECT * FROM main.tbl WHERE field2 = 'IN" and the error was: Execution finished with errors. Result: unable to open database: Indiana.db At line 1: ATTACH DATABASE 'Indiana.db' AS other; – SMan Jun 09 '22 at 03:36
  • So I'm able to select the records I need with: `SELECT * FROM table WHERE field2 IS 'IN';` but I don't know the command I should use to get the into another table or dumped to a CSV. – SMan Jun 10 '22 at 00:04

1 Answers1

0

Here is in steps what to do:

  1. 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...)"

  1. now open your source file with sqlite: sqlite3.exe mainfile.db

  2. 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;
  1. 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

MyICQ
  • 987
  • 1
  • 9
  • 25