将 SQL 数据从一个表移动到另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1612267/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Move SQL data from one table to another
提问by doubleplusgood
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.
例如，我需要将所有表行从 Table1 移动到 Table2，其中它们的用户名 = 'X' 和密码 = 'X'，以便它们不再出现在 Table1 中。
I'm using SQL Server 2008 Management Studio.
我正在使用 SQL Server 2008 Management Studio。
回答by Thorsten
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 new matching records being inserted into table1 between the two statements, you can add an and exists <in table2>.
这是最简单的形式。如果您必须担心在两个语句之间将新的匹配记录插入到 table1 中，您可以添加一个and exists <in table2>.
回答by that0th3rGuy
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 INSERTand then a DELETEmight 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.
正如一些人所提到的，执行 anINSERT然后执行aDELETE可能会导致完整性问题，所以也许一种绕过它并在单个语句中整齐地执行所有操作的方法是利用[deleted]临时表。
DELETE FROM [source]
OUTPUT [deleted].<column_list>
INTO [destination] (<column_list>)
回答by Ken Sands
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).
所有这些答案都对 INSERT 和 DELETE 运行相同的查询。如前所述，这可能会导致 DELETE 获取语句之间插入的记录，并且如果查询很复杂，可能会很慢（尽管聪明的引擎“应该”使第二次调用更快）。
The correct way (assuming the INSERT is into a fresh table) is to do the DELETE against table1 using the key field of table2.
正确的方法（假设 INSERT 是到一个新表中）是使用 table2 的键字段对 table1 执行 DELETE。
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.
请原谅我的语法，我在引擎之间跳跃，但你明白了。
回答by pirho
Yes it is. First INSERT + SELECT and then DELETE orginals.
是的。首先 INSERT + SELECT 然后 DELETE 原件。
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 UserIDor someother primary key, then you can use IDENTITY INSERTto preserve the key.
您可能想要保留UserID或其他主键，然后您可以使用IDENTITY INSERT来保留该键。
回答by GreySage
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)>
回答by Dheerendra Kulkarni
Use this single sql statement which is safe no need of commit/rollback with multiple statements.
使用这个单一的 sql 语句是安全的，不需要提交/回滚多个语句。
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
在 SQL 服务器上工作对 MySql 进行适当的更改
回答by Adriaan Stander
Try this
尝试这个
INSERT INTO TABLE2 (Cols...) SELECT Cols... FROM TABLE1 WHERE Criteria
Then
然后
DELETE FROM TABLE1 WHERE Criteria
回答by workmad3
You should be able to with a subquery in the INSERT statement.
您应该能够在 INSERT 语句中使用子查询。
INSERT INTO table1(column1, column2) SELECT column1, column2 FROM table2 WHERE ...;
followed by deleting from table1.
然后从table1中删除。
Remember to run it as a single transaction so that if anything goes wrong you can roll the entire operation back.
请记住将其作为单个事务运行，以便如果出现任何问题，您可以回滚整个操作。
回答by royse41
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
回答by abdkok
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
