Move SQL Data from One Table to Another

Move SQL data from one table to another

Should be possible using two statements within one transaction, an insert and a delete:

BEGIN TRANSACTION;
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>.

Copy data into another table

If both tables are truly the same schema:

INSERT INTO newTable
SELECT * FROM oldTable

Otherwise, you'll have to specify the column names (the column list for newTable is optional if you are specifying a value for all columns and selecting columns in the same order as newTable's schema):

INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable

How to move tables from one sql server database to another?

If you're moving the tables to a whole new database just because of growth, you might be better off considering using filegroups in your existing database instead. There will be a lot fewer headaches going forward than trying to deal with two separate databases.

EDIT

As I mentioned in my comments below, if you truly need a new database, depending on the total number of tables involved, it might be easier to restore a backup of the database under the new name and drop the tables you don't want.

Move a table from one database to another database SQL Server

If the databases are on same server then do it like this,

select * into DB_2.T1 from DB_1.[dbo].[T1]

if you have databases on different servers than you have to create a linked server.

On second thought you can generate "create tables scripts" and run them on second database

how can i move all data of one table to another table

You can try these queries:

This query will copy the data and structure, but the indexes are not included:

CREATE TABLE new_table SELECT * FROM old_table;

To copy everything, including database objects such as indexes, primary key constraint, foreign key constraints, triggers run these queries:

CREATE TABLE new_table LIKE old_table; 
INSERT new_table SELECT * FROM old_table;

To insert data into an existing table, use this :

INSERT INTO table2 SELECT * FROM table1 

How to move one table data to another table using stored procedure

Like this ?

... all your code...
-- create table department_cp with same structure as department but empty.
CREATE TABLE department_cp AS SELECT * FROM department WHERE 1 = 2;

SELECT * FROM department_cp;

-- nothing

CREATE OR REPLACE procedure copy_data
AS
BEGIN
  INSERT INTO department_cp SELECT * FROM department;
END;
/

exec copy_data;

SELECT * FROM department_cp;

-- same as department.

So what is your actual question ?

Schedule a SQL-query to move data from one table to another with Azure SQL-db

There are multiple ways to run automated scripts on Azure SQL Database as below:

1. Using Automation Account Runbooks.
2. Using Elastic Database Jobs in Azure
3. Using Azure Data factory.

As you are running just one script, I would suggest you to take a look into Automation Account Runbooks. As an example below, a PowerShell Runbook to execute the statement.

$database = @{
'ServerInstance' = 'servername.database.windows.net'
'Database' = 'databasename' 
'Username' = 'uname'
'Password' = 'password'
'Query' = 'DELETE FROM Events OUTPUT DELETED.* INTO archieveevents'    
}
Invoke -Sqlcmd @database

Then, it can be scheduled as needed:

---

Related Topics