SQL - Copy a row from one table to another

fean0r

Heya,

Just quick question I would like to copy a row from a table called users to another table called users_old.

Is there a simple way to do this?

I can query the table/row and copy each value but I assume there might be 1 command to do it.

Let me know, thanks in advance.

NogDog

The first question needs to be, "Why?" If you have two tables duplicating the same data, there's probably something wrong with your database design.

Anyway, one possibility would be to simply do it in the insert query:

INSERT INTO table1 (col1, col2, col3) 
  SELECT table2.col1, table2.col2, table2.col3 
    FROM table2 WHERE table2.col2 = '123'

fean0r

NogDog wrote:
If you have two tables duplicating the same data, there's probably something wrong with your database design.

There is nothing wrong with the design - when a user is deleted I wanted to add them to user_old table in case I need to reference any of there information again.

Thanks for your suggestion.

leatherback

Hi Feanor:

There is technically nothing wrong, however, you are setting yourself up for trouble (Imagine the scenario where you would like to add a field to the table..)

Instead of duplicating the table, add a colum to your usertable, with status, e.g.,
0 = inactive, yet new
1 = Active
2 = Old
3 = banned
4 = Honorary

And you have no need to copy, nor duplicate tables.

fean0r

Yes I understand that however if you have 10000 rows in a table and 4000 of them are never used its a waste of processing/loading times.

leatherback

Sure.
Your choice! 😃