英文:
How to migrate data to new table with some default value from old table in SQL
问题
我有一个名为User的表，其中包含字段username和password。我想创建一个名为tracking_user的表，该表包含以下字段：username、password、custom和time。请问我该如何创建SQL脚本来创建tracking_user表，其中的username和password字段来自user表，custom字段的默认值为0，而time字段的值应该是运行脚本时的当前时间。非常感谢！
英文:
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!
答案1
得分: 2
- 克隆您的用户表结构：
CREATE TABLE tracking_user LIKE user;
- 向新表添加额外列：
ALTER TABLE tracking_user ADD custom INT(11) NOT NULL DEFAULT '0' AFTER password, ADD time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER custom;
- 将您的用户表数据克隆到新表：
INSERT INTO tracking_user (username, password) SELECT username, password FROM user;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论