sqlsqlalchemy-migrate

How to migrate data to new table with some default value from old table in SQL

I have table User with field username, password. I want to create tracking_user table with these field: username, password, custom, time. How can I create SQL script to create tracking_user with username, password from user table and custom have default value 0 and time is now when running script. Thank you so much!

Solution

  • For that you need to create 3 different SQL queries like below.

    1) Clone your user table structure

    CREATE TABLE tracking_user LIKE user;

    2) Add additional column to new table

    ALTER TABLE tracking_user ADD custom INT(11) NOT NULL DEFAULT '0' AFTER password, ADD time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER custom;

    3) Clone your user table data to new table

    INSERT INTO tracking_user (username, password) SELECT username, password FROM user;