Move SQL data from one table to another

0 votes
asked Oct 23, 2009 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.

I'm using SQL Server 2008 Management Studio.

Thank you.

11 Answers

0 votes
answered Jan 23, 2009 by adriaan-stander

Try this

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

Then

DELETE FROM TABLE1 WHERE Criteria
0 votes
answered Jan 23, 2009 by workmad3

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.

0 votes
answered Jan 23, 2009 by seanxe

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
0 votes
answered Jan 23, 2009 by pirho

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

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

0 votes
answered Jan 21, 2011 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).

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.

0 votes
answered Jan 27, 2014 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 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>)
0 votes
answered Jan 15, 2015 by user1847437

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)
0 votes
answered Jan 24, 2016 by gpdude

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
0 votes
answered Jan 28, 2016 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
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...