On 2015-03-07 9:59 AM, Simon Slavin wrote: > On 7 Mar 2015, at 4:42pm, Dave <theschemer at cox.net> wrote: > >> I am fairly new at this although I have wanted to learn and tried again and >> again...But I have a problem. I created a database and probably did it wrong >> and I am trying to fix it. I made a database with 7 tables in it all with a >> primary key and a record ID that matches the primary key. Now when trying to >> use the database I see that I should have made 1 table with all the related >> data (I think) and am trying to copy one column of data at a time to the >> "main" table. Can that be done and if so how? > > Without going into your situation in detail, I have a suggestion which may > help you approach the problem another way. The SQLite shell tool has a > '.dump' command which turns a database into SQL commands, and a '.read' > command which uses the commands to create schema and data in a new database. > > So dump the database into a text file. Then you can use editing tools > (usually global find-and-replace) mess with the text file so that all the > inserting is done to the same table. Then you can create your new database > by reading the altered text file.
Frankly the idea (proposed by Simon here) of solving this by dumping everything to a text file and manipulating it there with editing tools sounds abysmal to me. The only time one might consider that reasonable is if the total number of records is just a handful and you're essentially just re-entering them from scratch. Once you've already got your data in SQLite, the best general solution by far is to use SQL to manipulate it; if you can't, you've already lost. What you want to do is create new table(s) with the new format you want, and then do INSERT INTO <new> SELECT FROM <old> such that the SELECT easily and reliably does all the hard work of collecting up all the data from the old tables and rearranging it into the new format. Depending on the complexity of the task, you may also create temporary tables for intermediate stages of the processing. Solving the problem with the likes of SQL UPDATE is hard, but using SELECT is easy. By a similar token, I believe SQL is often the best place to clean up data from external sources. Create temporary tables that are very lax in format and constraints that take the external data as pristine as possible, load into those, and then use SELECTs/etc to derive cleaner versions from those into the final tables (or other intermediaries), and you can use the SQL powers to filter or compensate for dirty data etc. Especially useful for dealing with duplicate data in the source, find or handle with SELECT GROUP BY etc rather than trying conditional INSERT logic or what have you. -- Darren Duncan
