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
