On Mon, Apr 19, 2010 at 1:00 PM, Pavel Ivanov <paiva...@gmail.com> wrote: >> In other words, unless it grossly and severely bothers your >> sensibilities, don't waste your time on this exercise. > > AND unless application developer for some weird reason used '*' in > select statements. > >
In other words, don't use SELECT * for production purposes, unless there is a compelling reason to do so, or you are just incorrigibly lazy. However, I should clarify one assertion of mine. I wrote, "for the most part, the order in which the columns appear in your table schema is irrelevant." Which begs the question, when could placing columns a certain way be strategically advantageous? For example, when you have a blob column. Placing a blob column in between other columns can reduce speed if you want to get to only the other columns. That is because a blob may not fit in one page, and may go into overflow pages, thereby causing other columns beyond it to also go into the overflow pages. In such a case, it is advantageous to have a blob col at the very end of your schema. Or, even better, add an additional table that holds only a linking id col and the blob col, and have a one-to-one relationship between your master table and the blob table. I must also note, as a perl enthusiast, laziness *is* a virtue, but practice safe laziness. > Pavel > > On Mon, Apr 19, 2010 at 1:56 PM, P Kishor <punk.k...@gmail.com> wrote: >> On Mon, Apr 19, 2010 at 12:47 PM, sabapathy <sabapathy...@rediffmail.com> >> wrote: >>> >>> The DB had some 15 columns before. >>> And there are lot of records saved using the s/w tool. >>> But in the latest version of tool there are some columns added in DB >>> inbetween of existing columns. >>> So to use the DB saved using previous version of tool, I need to add some >>> columns(blank) in between in the old DB. >>> But I came to know that new columns can be added only at the end. >> >> >> First, and most importantly, you should realize that for the most >> part, the order in which the columns appear in your table schema is >> irrelevant. You might, for aesthetic reasons, want to insert some >> columns in between, but the db doesn't and shouldn't care. You can >> always reorder the display of your selected output by simply changing >> the order of the columns in your SELECT statement. >> >> In other words, unless it grossly and severely bothers your >> sensibilities, don't waste your time on this exercise. That said... >> >>> So I have to create an empty table in new format and insert the records from >>> old table & delete the old table(?). >>> How can I do this efficiently even if there are tens of thousands of >>> records..? >> >> CREATE TABLE newtable (columns in your desired order); >> INSERT INTO newtable (columns that exist in old table) VALUES SELECT >> FROM oldtable <columns in your desired order>; >> >> >> See http://www.sqlite.org/lang_insert.html >> >> "The second form of the INSERT statement takes its data from a SELECT >> statement. The number of columns in the result of the SELECT must >> exactly match the number of columns in the table if no column list is >> specified, or it must match the number of columns named in the column >> list. A new entry is made in the table for every row of the SELECT >> result. The SELECT may be simple or compound." >> >>> >>> Can I provide mapping of columns to insert from one table to another? >>> (For eg, if the old table has 2 columns & new one 3 columns, I want to >>> insert 1st column to 1st column, 2nd to 3rd and leave 2nd column empty in >>> new table) >>> >>> Thanks.. >>> -- >>> View this message in context: >>> http://old.nabble.com/Inserting-from-other-table-tp28287723p28287723.html >>> Sent from the SQLite mailing list archive at Nabble.com. >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> >> >> -- >> Puneet Kishor http://www.punkish.org >> Carbon Model http://carbonmodel.org >> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org >> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor >> Nelson Institute, UW-Madison http://www.nelson.wisc.edu >> ----------------------------------------------------------------------- >> Assertions are politics; backing up assertions with evidence is science >> ======================================================================= >> _______________________________________________ >> 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
