Move SQL data from one table to another

Question

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.

Answer 1

This is an ancient post, sorry, but I only came across it now and I wanted to give my solution to whoever might stumble upon this one day.

As some have mentioned, performing an INSERT and then a DELETE might lead to integrity issues, so perhaps a way to get around it, and to perform everything neatly in a single statement, is to take advantage of the [deleted] temporary table.

DELETE FROM [source] OUTPUT [deleted].<column_list> INTO [destination] (<column_list>) 

Answer 2

Should be possible using two statements within one transaction, an insert and a delete:

BEGIN TRANSACTION; INSERT INTO Table2 (<columns>) SELECT <columns> FROM Table1 WHERE <condition>;  DELETE FROM Table1 WHERE <condition>;  COMMIT; 

This is the simplest form. If you have to worry about new matching records being inserted into table1 between the two statements, you can add an and exists <in table2>.