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

Question

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!

Answer 1

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;