[SOLVED] Splitting an SQLite table into two.

[Fangbeast]

I need to split a table into two to cut down on database sizes but don't know how. The original table is monolithic and contains a lot of duplications so a split is in order.

The 'Item' table will contain a list of distinct items along with their associated data.

The 'history' table will contain a list of how often those items were purchased and the 'ItemId' column will contain the record number of the parent item in the 'Item' table.

I've split the tables but can't get the ItemId record right. Working with more than one table like this is not something I normally do. Anyone know how to frame an SQLite query to do this?

Code: Select all

Original table     New item table   History table

item                  item                  ItemId
category            category
brand                brand
manufacturer     manufacturer
boughtfrom                               boughtfrom
boughton                                  boughton
quantity                                    quantity
units                units
weight             weight
aisle                                          aisle
price                                         price
discount                                    discount
total                                          total
note                note
picture            picture
barcode           barcode
deleted           deleted
record             record                record

So, if 'Item' from the 'Item' table is 'Spam' and the record number is 84 and has been bought 4 times, the History table should have 4 records with the ItemId being 84.

**UPDATE** Solved by email from the Nabble SQLite support forum.

[bohne_68]

Hallo,

I don't thing that that the file size (table size) is the problem.

Do you tried to reduce the number of received record's? Usually you see only 50 lines in a table/grid .
This depends on your workflow, queries etc.

Or you have to improve/cleanup your database using "external" tools.

regards
Thomas