Insert new column into table in sqlite

Question

I have a table with columns name  qty  rate  Now I need to add a new column COLNew in between the name and qty columns  How do I add a new column in between two columns

User · Answer

SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table.

If you want to make more complex changes in the structure of a table, you will have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

For example, suppose you have a table named "t1" with columns names "a" and "c" and that you want to insert column "b" from this table. The following steps illustrate how this could be done:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,c);
INSERT INTO t1_backup SELECT a,c FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b, c);
INSERT INTO t1 SELECT a,c FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

Now you are ready to insert your new data like so:

UPDATE t1 SET b='blah' WHERE a='key'

User · Answer

You can add new column with the query  ALTER TABLE TableName ADD COLUMN COLNew CHAR 25    But it will be added at the end  not in between the existing columns

User · Answer

ALTER TABLE  tableName  ADD COLUMN COLNew  type   UPDATE  tableName  SET COLNew    base on  type  pass value here     This update is required to handle the null value  inputting a default value as you require  As in your case  you need to call the SELECT query and you will get the order of columns  as paxdiablo already said    SELECT name  colnew  qty  rate FROM tablename    and in my opinion  your column name to get the value from the cursor   private static final String ColNew  ColNew   String val cursor getString cursor getColumnIndex ColNew      so if the index changes your application will not face any problems   This is the safe way in the sense that otherwise  if you are using CREATE temptable or RENAME table or CREATE  there would be a high chance of data loss if not handled carefully  for example in the case where your transactions occur while the battery is running out

User · Answer

You don t add columns between other columns in SQL  you just add them  Where they re put is totally up to the DBMS  The right place to ensure that columns come out in the correct order is when you select them   In other words  if you want them in the order  name colnew qty rate   you use   select name  colnew  qty  rate from       With SQLite  you need to use alter table  an example being   alter table mytable add column colnew char 50

User · Answer

You have two options  First  you could simply add a new column with the following   ALTER TABLE  tableName  ADD COLUMN COLNew  type     Second  and more complicatedly  but would actually put the column where you want it  would be to rename the table   ALTER TABLE  tableName  RENAME TO TempOldTable    Then create the new table with the missing column   CREATE TABLE  tableName   name TEXT  COLNew  type  DEFAULT  defaultValue   qty INTEGER  rate REAL     And populate it with the old data   INSERT INTO  tableName   name  qty  rate  SELECT name  qty  rate FROM TempOldTable    Then delete the old table   DROP TABLE TempOldTable    I d much prefer the second option  as it will allow you to completely rename everything if need be

User · Answer

I was facing the same problem and the second method proposed in the accepted answer  as noted in the comments  can be problematic when dealing with foreign keys   My workaround is to export the database to a sql file making sure that the INSERT statements include column names  I do it using DB Browser for SQLite which has an handy feature for that  After that you just have to edit the create table statement and insert the new column where you want it and recreate the db   In  nix like systems is just something along the lines of   cat db sql   sqlite3 database db   I don t know how feasible this is with very big databases  but it worked in my case

[sqlite] Insert new column into table in sqlite?

Examples related to sqlite