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
Copy Row from one table to another
Moderator: General Moderators
-
waskelton4
- Forum Contributor
- Posts: 132
- Joined: Mon Sep 09, 2002 6:42 pm
- scorphus
- Forum Regular
- Posts: 589
- Joined: Fri May 09, 2003 11:53 pm
- Location: Belo Horizonte, Brazil
- Contact:
You could rename the table and create a new one. For example:
These operations run very fast except the create:
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.
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;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)Hope that helps,
Scorphus.
This works:
Code: Select all
INSERT INTO
table_2
SELECT
*
FROM
table_1
WHERE
condition = met;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.