Move SQL data from one table to another
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.
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>.
Move SQL data from one table to another, Should be possible using two statements within one transaction, an insert and a delete: INSERT INTO Table2 (<columns>) SELECT <columns> Migrate And Deploy Database Changes In Visual Studio With Migration Scripts. SQL Change Automation Is A Capability Of The SQL Toolbelt.
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>)
Optimize Moving SQL Server Data From One Table to Another Table, In this tip we look at different ways to move SQL Server data from one table to another table as well as the results I saw with each approach. 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.
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.
SQL INSERT INTO SELECT Statement, INSERT INTO SELECT requires that data types in source and target tables match; The existing Copy only some columns from one table into another table:. You can copy data from one server to another the same way by using fully qualified linked server names (<linked_server>.<database>.<owner>.<table_name>). The DTS Import/Export Wizard method. Another method to copy data between tables is to use Data Transformation Services (DTS). The easiest way to do this is to use the DTS Import/Export Wizard.
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.
How to copy tables from one database to another in SQL Server, You have the choice to export from the source database or import from the destination one in order to transfer the data: From SQL Server Basically, the SWITCH TO command is available for moving data between partitions from different tables. We use the ALTER TABLE command to move the data to a new partition Test_Mst_History_New. Here the partition is not allocated, which means the data moves across the tables.
INSERT INTO TABLE2 (Cols...) SELECT Cols... FROM TABLE1 WHERE Criteria
DELETE FROM TABLE1 WHERE Criteria
Six different methods to copy tables between databases in SQL Server, In SQL Server, copying tables between the databases of the same database, and we're going to move it over to another instance of SQL. In object Explorer , go to source database and select table to move. Right click , Script Table As -> CREATE TO -> New Query Editor Window. This opens query window with the SQL queries specifying schema , indexes , constraints on the table. You can change table name in CREATE TABLE section and make other changes
Transferring Data from One Table to Another, Every DBA needs to transfer data between tables. This is uses the INSERT INTO T-SQL statement to move records from one table to another. if both databases are on same server and you want to transfer entire table (make copy of it) then use simple select into statement You can then write cursor top of sysobjects and copy entire set of tables that way. If you want more complex data extraction & transformation, then use SSIS and build appropriate ETL in it.
Moving rows from one table to another, My assumption for this solution is that the reason the for the insert failure is some sort of trappable SQL error (such as a key violation, datatype In the Specify Table Copy or Query step of the SQL Server Import and Export Wizard, choose the Copy data from one or more tables or views option and click the Next button:
Move data from one table to another using sql – SQLServerCentral, Two tables are same and Test2 is the backup table for Test1. Now i need to do some checking in Test1. So i need to move all the data from As we know, data is stored on all data files, so as per our requirement we have to migrate data from one data file to others. We will use the DBCC SHRINKFILE command to migrate the data. The DBCC SHRINKFILE command shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same filegroup.