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

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.

例如，我需要将所有表行从表1移动到表2，其中它们的用户名= 'X'和密码= 'X'，以便它们不再出现在表1中。

I'm using SQL Server 2008 Management Studio.

我使用的是SQL Server 2008管理工作室。

Thank you.

谢谢你！

12 个解决方案

#1

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

这是最简单的形式。如果您必须担心将新的匹配记录插入到两个语句之间的表1中，您可以在表2>中添加一个and exist。

#2

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

#3

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查询。正如前面所提到的，如果查询很复杂，则可能导致删除在语句之间插入的记录，并且速度会很慢(尽管聪明的引擎“应该”让第二次调用更快)。

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

正确的方法(假设插入是到一个新表中)是使用表2的key字段对表1执行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.

不好意思，我在搜索引擎之间跳来跳去，但是你们懂的。

#4

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.

您可能希望保存UserID或其他主键，然后可以使用IDENTITY INSERT来保存该键。

see more on SET IDENTITY_INSERT on MSDN

请参阅MSDN上的SET IDENTITY_INSERT

#5

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.

然后从表1中删除。

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

记住将它作为单个事务运行，这样如果出现问题，您可以将整个操作滚回。

#6

Try this

试试这个

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

Then

然后

DELETE FROM TABLE1 WHERE Criteria

#7

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

#8

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

#9

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 server上工作，为MySql做适当的更改

#10

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

如果两个表使用相同的ID或具有共同的唯一键:

1) Insert the selected record in table 2

1)在表2中插入选定的记录

INSERT INTO table2 SELECT * FROM table1 WHERE (conditions)

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

2)如表2所示，从表1中删除所选记录。

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

#11

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

#12

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

#1