The appropriate structure of the database depends on whether you need the
tables spread out into multiple tables for consistency ("one fact in one
location") -- a process called "normalization or whether you want all the
data in one table for ease of querying ("denormalization").Transactional systems are usually designed to be normalized; while decision support tables may be more de-normalized (for ease of querying). If you want to combine your tables along the primary key, the way to do it in SQL is to create SELECT ...JOIN queries and then when you have a working join query that has the struture you want you have a choice of using the SELECT...JOIN query as if it was table or converting the query to a table with a CREATE TABLE FROM SELECT ...JOIN query. Jim Callahan Orlando, FL On Sat, Mar 7, 2015 at 8:18 PM, Darren Duncan <darren at darrenduncan.net> wrote: > 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 > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
