Page 1 of 1
Copy Row from one table to another
Posted: Fri Mar 12, 2004 1:45 pm
by waskelton4
is there a single sql statement i can use to move a row of data in one table to a row in a different table?
the are exactly the same..
using to archive data and keep the db fast..
any help is greatly appreciated.
thanks
will
Posted: Sat Mar 13, 2004 3:12 pm
by scorphus
You could rename the table and create a new one. For example:
Code: Select all
CREATE TABLE holyday_new (
idholyday INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
who VARCHAR(32) NULL,
from_date DATE NULL,
to_date DATE NULL,
fd_hd CHAR(2) NULL,
PRIMARY KEY(idholyday)
);
RENAME TABLE holyday_bak TO holyday_old;
RENAME TABLE holyday TO holyday_bak;
RENAME TABLE holyday_new TO holyday;
DROP TABLE holyday_old;
These operations run very fast except the create:
Code: Select all
mysql> CREATE TABLE holyday_new (
-> idholyday INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> who VARCHAR(32) NULL,
-> from_date DATE NULL,
-> to_date DATE NULL,
-> fd_hd CHAR(2) NULL,
-> PRIMARY KEY(idholyday)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> RENAME TABLE holyday_bak TO holyday_old;
Query OK, 0 rows affected (0.00 sec)
mysql> RENAME TABLE holyday TO holyday_bak;
Query OK, 0 rows affected (0.00 sec)
mysql> RENAME TABLE holyday_new TO holyday;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DROP TABLE holyday_old;
Query OK, 0 rows affected (0.00 sec)
Since the only 'long' operation is the first and maybe the last (CREATE and DROP) and the table won't exist only between those 'RANAME' oprations, which are fast, I think this might not cause some error.
Hope that helps,
Scorphus.
Posted: Mon Mar 15, 2004 5:54 pm
by pickle
This works:
Code: Select all
INSERT INTO
table_2
SELECT
*
FROM
table_1
WHERE
condition = met;
Posted: Mon Mar 15, 2004 6:09 pm
by JAM
While speaking about backup's, I just want to mention:
http://www.mysql.com/doc/en/mysqlhotcopy.html or
http://www.mysql.com/doc/en/BACKUP_TABLE.html (depending on sql version) as other solutions.