Search code examples
sqldefaultsql-insertspatialite

SQL/SpatiaLite: How to insert a default value into certain columns when inserting data from another table?

I have a new table, with these columns (all NOT NULL):

creation_date, created_by, attribute_1, attribute_2, geometry

I also have an old table:

attribute_1, attribute_2, geometry

I want to insert the data from the old table through INSERT INTO / SELECT / FROM. However this obviously gives me an error for the creation_date and created_by columns because the old table does not have any. How do I insert a default value for these colmuns while inserting the rows from the old table?

Solution

  • You should simply choose some literals. Unless that data exists elsewhere and you can look it up with a join, I would suggest something like:

    INSERT INTO newtable
SELECT date('now') as creation_date, 
'Legacy Data' as created_by, attribute_1, attribute_2, geometry
FROM oldtable