Move SQL data from one table to another

Question

Hi there,

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.

Thank you.

Answer 1

Try this

INSERT INTO TABLE2 (Cols...) SELECT Cols... FROM TABLE1 WHERE Criteria

Then

DELETE FROM TABLE1 WHERE Criteria

Answer 2

You should be able to with a subquery in the INSERT statement.

INSERT INTO table1(column1, column2) SELECT column1, column2 FROM table2 WHERE ...;

followed by deleting from table1.

Remember to run it as a single transaction so that if anything goes wrong you can roll the entire operation back.

Answer 3

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

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 about new matching records being inserted into table1 between the two statements, you can add an and exists <in table2>.

Answer 4

You could try this:

SELECT * INTO tbl_NewTableName 
FROM tbl_OldTableName
WHERE Condition1=@Condition1Value

Then run a simple delete:

DELETE FROM tbl_OldTableName
WHERE Condition1=@Condition1Value

Answer 5

Yes it is. First INSERT + SELECT and then DELETE orginals.

INSERT INTO Table2 (UserName,Password) SELECT UserName,Password FROM Table1 WHERE UserName='X' AND Password='X'

then delete orginals

DELETE FROM Table1 WHERE UserName='X' AND Password='X'

you may want to preserve UserID or someother primary key, then you can use IDENTITY INSERT to preserve the key.

see more on SET IDENTITY_INSERT on MSDN