How to combine two tables into one?
Suppose I have table users with cols user_id, username, pwd, and table user_status with cols user_id, is_logged. What is the best way to obtain a new table with cols user_id, username, pwd, is_logged with all my data in place?
See:
1. CREATE TABLE LIKE syntax (http://www.cubrid.org/manual/840/en/CREATE%20TABLE%20LIKE)
2. CREATE TABLE AS SELECT syntax (http://www.cubrid.org/manual/840/en/CREATE%20TABLE%20AS%20SELECT)
3. INSERT SELECT syntax (http://www.cubrid.org/manual/840/en/INSERT%20%E2%80%A6%20SELECT%20Statement)
4. UNION syntax http://www.cubrid.org/manual/840/en/Statement%20Set%20Operators
Basically, you can do something like this:
CREATE TABLE a (i INTEGER); INSERT INTO a(i) VALUES(1),(2),(3); CREATE TABLE b (i INTEGER); INSERT INTO b(i) VALUES(4),(5),(6); CREATE TABLE copy_a_b LIKE a; -- insert everything from a and b: INSERT INTO copy_a_b SELECT * FROM a UNION ALL SELECT * FROM b; -- do not insert duplicates: INSERT INTO copy_a_b SELECT * FROM a UNION SELECT * FROM b;
CREATE TABLE users(user_id CHAR(20), username VARCHAR(64), pwd VARCHAR(128)); CREATE TABLE user_status (user_id CHAR(20), is_logged INTEGER); CREATE TABLE copy_user_staus AS SELECT u.user_id user_id, u.username username, u.pwd pwd, us.is_logged is_logged FROM users u, user_status us WHERE u.user_id = us.user_id;