将 SQL 数据从一个表移动到另一个表

Try this

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

Then

DELETE FROM TABLE1 WHERE Criteria

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.

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>.

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

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

All these answers run the same query for the INSERT and DELETE. As mentioned previously, this risks the DELETE picking up records inserted between statements and could be slow if the query is complex (although clever engines "should" make the second call fast).

The correct way (assuming the INSERT is into a fresh table) is to do the DELETE against table1 using the key field of table2.

The delete should be:

DELETE FROM tbl_OldTableName WHERE id in (SELECT id FROM tbl_NewTableName)

Excuse my syntax, I'm jumping between engines but you get the idea.

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>)

If the two tables use the same ID or have a common UNIQUE key:

1) Insert the selected record in table 2

INSERT INTO table2 SELECT * FROM table1 WHERE (conditions)

2) delete the selected record from table1 if presents in table2

DELETE FROM table1 as A, table2 as B WHERE (A.conditions) AND  (A.ID = B.ID)

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

You may use "Logical Partitioning" to switch data between tables:

By updating the Partition Column, data will be automatically moved to the other table:

here is the sample:

CREATE TABLE TBL_Part1
(id  INT NOT NULL,
val VARCHAR(10) NULL,
PartitionColumn  VARCHAR(10) CONSTRAINT CK_Part1 CHECK(PartitionColumn = 'TBL_Part1'),
CONSTRAINT TBL_Part1_PK PRIMARY KEY(PartitionColumn, id)
);


CREATE TABLE TBL_Part2
(id  INT NOT NULL,
val VARCHAR(10) NULL,
PartitionColumn  VARCHAR(10) CONSTRAINT CK_Part2 CHECK(PartitionColumn = 'TBL_Part2'),
CONSTRAINT TBL_Part2_PK  PRIMARY KEY(PartitionColumn, id)
);


GO


CREATE VIEW TBL(id, val, PartitionColumn)
WITH SCHEMABINDING
AS
SELECT id, val, PartitionColumn FROM dbo.TBL_Part1
UNION ALL
SELECT id, val, PartitionColumn FROM dbo.TBL_Part2;


GO


--Insert sample to TBL ( will be inserted to Part1 )
INSERT INTO TBL
VALUES(1, 'rec1', 'TBL_Part1');


INSERT INTO TBL
VALUES(2, 'rec2', 'TBL_Part1');


GO


--Query sub table to verify
SELECT * FROM TBL_Part1


GO
--move the data to table TBL_Part2 by Logical Partition switching technique
UPDATE TBL
SET
PartitionColumn = 'TBL_Part2';


GO


--Query sub table to verify
SELECT * FROM TBL_Part2

Use this single sql statement which is safe no need of commit/rollback with multiple statements.

INSERT Table2 (
username,password
) SELECT username,password
FROM    (
DELETE Table1
OUTPUT
DELETED.username,
DELETED.password
WHERE username = 'X' and password = 'X'
) AS RowsToMove ;

Works on SQL server make appropriate changes for MySql

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)>

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

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.