Move SQL data from one table to another

Question

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

User · Answer

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

User · Answer

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   lt column list gt  INTO  destination    lt column list gt

User · Answer

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

User · Answer

A cleaner representation of what some other answers have hinted at   DELETE sourceTable OUTPUT DELETED   INTO destTable  Comma  separated  list  of  columns  WHERE  lt conditions  if any  gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Should be possible using two statements within one transaction  an insert and a delete  BEGIN TRANSACTION  INSERT INTO Table2   lt columns gt   SELECT  lt columns gt  FROM Table1 WHERE  lt condition gt    DELETE FROM Table1 WHERE  lt condition gt    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  lt in table2 gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try this  INSERT INTO TABLE2  Cols     SELECT Cols    FROM TABLE1 WHERE Criteria   Then  DELETE FROM TABLE1 WHERE Criteria

[sql] Move SQL data from one table to another

Examples related to sql

Examples related to sql-server