How do I delete only the results of one table in a database in MySQL and replicate to another remote empty table with the same structure?

Question

This is a long sentence, does anyone know?

+2

user198729 Apr 25. 10 at 8:24

2 answers

codaddict · Answer 1 · 2010-04-25T08:43:20+0000

To create dump

:

mysqldump --user [username] --password=[password] --no-create-info [database name] [table name] > /tmp/dump.sql

Recovery:

mysql --u [username] --password=[password] [database name] [table name] < /tmp/dump.sql

NitroxDM · Answer 2 · 2010-04-25T08:33:58+0000

Something like that:

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename
WHERE 1=0

I'm not sure if this works with My SQL, but you get the idea.

It does NOT duplicate PCs, FKs, indexes, stored procedures, or whatever. Just columns and data types.

D'o

I must have misunderstood your question. It's almost 3 hours before bed.

So, there might be a better way to do it, but this will get the job done.

SELECT  CONCAT('INSERT INTO (COL1, COL2) VALUES (',COL1,COL2,');');

You will need to add 'for some data types.

SELECT  CONCAT('INSERT INTO (COL1, COL2) VALUES (',COL1,''' ''',COL2,');');

Take the output and run on the remote db.