Move SQL data from one table to another
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.
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>.