Probably these commands would help: CREATE TABLE temp_forms (form TEXT, root TEXT); .separator "|" .import forms.txt forms CREATE TABLE roots(id INTEGER PRIMARY KEY, root TEXT); INSERT INTO roots (root) SELECT DISTINCT root FROM temp_forms; CREATE TABLE forms (form TEXT, root INTEGER); INSERT INTO forms (form, root) SELECT a.form, b.id FROM temp_forms a, roots b WHERE a.root = b.root; DROP TABLE temp_forms;
Note that for storing root strings I use separate table, so that each string is stored only once... Pavel On Fri, Jan 8, 2010 at 8:23 AM, P.C. <cavalo...@gmail.com> wrote: > Good evening, > > Suppose I have a text file named forms.txt with the following content: > > form1|root1 > form2|root1 > form3|root1 > form4|root2 > form5|root2 > form6|root2 > form7|root3 > > Here, I use the character "|" for separating columns. Then, I create a > database and give it the following commands: > > CREATE TABLE forms (form TEXT, root TEXT); > .separator "|" > .import forms.txt forms > CREATE INDEX key ON forms (form); > .quit > > As you can see, I create a table "forms" with two columns (form, root) > and import the content of forms.txt to this table, by using "|" as a > column separator. So far, so good. > But notice that, in the column "form", there are several values that > are associated with a same value in the column "root"; for example, > (form1, form2, form3) all belong to (root1). So, if there are several > forms associated to one root, this same root gets repeated several > times (for example, notice how "root1" is written for every form). > I was wondering whether I can associate forms of the "form" column to > a particular root in the "root" column in such a way that I don't need > to repeat the same root for every form that belongs to that root. That > would considerably reduce the database size. > Any ideas? > > Thank you in advance. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
