Move SQL data from one table to another

user_138630 0
Viewed 258272

I was wondering if it is possible to move all rows of data from one table to another, that match a certain query?

For example, I need to move all table rows from Table1 to Table2 where their username = 'X' and password = 'X', so that they will no longer appear in Table1.

I'm using SQL Server 2008 Management Studio.

sql-server sql
14 Answers

A cleaner representation of what some other answers have hinted at:

DELETE sourceTable
OUTPUT DELETED.*
INTO destTable (Comma, separated, list, of, columns)
WHERE <conditions (if any)>
user_48724770

Here is how do it with single statement

WITH deleted_rows AS (
DELETE FROM source_table WHERE id = 1
RETURNING *
) 
INSERT INTO destination_table 
SELECT * FROM deleted_rows;

EXAMPLE:

    postgres=# select * from test1 ;
 id |  name
----+--------
  1 | yogesh
  2 | Raunak
  3 | Varun
(3 rows)


postgres=# select * from test2;
 id | name
----+------
(0 rows)


postgres=# WITH deleted_rows AS (
postgres(# DELETE FROM test1 WHERE id = 1
postgres(# RETURNING *
postgres(# )
postgres-# INSERT INTO test2
postgres-# SELECT * FROM deleted_rows;
INSERT 0 1


postgres=# select * from test2;
 id |  name
----+--------
  1 | yogesh
(1 row)

postgres=# select * from test1;
 id |  name
----+--------
  2 | Raunak
  3 | Varun
user_48510210

It will create a table and copy all the data from old table to new table

SELECT * INTO event_log_temp FROM event_log

And you can clear the old table data.

DELETE FROM event_log

user_38557230

For some scenarios, it might be the easiest to script out Table1, rename the existing Table1 to Table2 and run the script to recreate Table1.

user_179060280
Related