How to Move Table & Data?

Moving a table from one database to another or to a different SQL Server Instance is the most common request that DBA works on. Developers implement and test their database design on a development database server. A request is opened to DBA team to move their database design to productions database server whenever they are ready to go live in production. The database design includes moving tables, table's data, stored procedures, functions, triggers etc. from development to production database Instance. With this article, I am going to show you the multiple ways to move table & data from one database to another database within same SQL Instances and also to a different Instance. My demo will include the use of SQL script and Linked Server but ignores SSIS, Export and Import wizard and other utilities.

To demo the table and data move, we have hr_stage and hr_db databases on both production and development SQL Instances. The dev_sql and prod_sql  are the development and production SQL Instance respectively. The hr_stage database on development SQL Instance has an employee table with 14 records. Rest of the Instance and databases are empty. You can get the detail on our demo table employee [here.]

The demo will cover following scenarios

1. Table structure move from one Database to another
2. Table structure move from one Instance to another 
3. Table structure along with data move from one Database to another
4. Table structure along with data move from one Instance to another
5. Data only move from one Database to another
6. Data only move from one Instance to another

Let's Begin!

Table Structure Move: 

To Another Database: You are asked to move employee table from hr_stage db to hr_db db within a dev_sql Instance. This move includes the structure of the table only but ignores  database constraints and table data.

SELECT empp, 
       ename, 
       job, 
       mgr, 
       hiredate, 
       sal, 
       comm, 
       dept 
INTO   [hr_db].dbo.employee 
FROM   [hr_stage].dbo.employee 
WHERE  1 = 2; 

The table employee is created under hr_db database on development SQL Instance.This table is modeled after employee on hr_stage.  The Where Clause here filters data because the conditions will never be matched. 

To Another SQL Instance: Here, you are asked to move employee table from hr_db  db of dev_sql Instance to hr_db of prod_sql. This move includes the structure of the table but ignores database constraints and its data.

SELECT empp, 
       ename, 
       job, 
       mgr, 
       hiredate, 
       sal, 
       comm, 
       dept 
INTO   [hr_db].dbo.my_employee 
FROM   [dev_sql_link].[hr_stage].dbo.employee 
WHERE  1 = 2; 

This SQL was executed on prod_sql Instance. The production Instance connects to development Instance via a linked server (dev_sql_link). Creating a linked server is out of scope here but you can get the detail on link server from my site. The Where Clause here filters data because the conditions will never be satisfied. 

The above SQL only moves the structure not the Constraints.There are no SQL command that can copy table Constraints along with the table. You are required to manually create the database Constraints and Indexes. You may use SSMS to generate the DDL for Constraints and Indexes.

Table  Move Along with Data:

To Another Database: Here, you are asked to move employee table along with data from hr_stage to hr_db of dev_sql Instance.This move includes the structure of the table but ignores database Constraints and it's data.

SELECT empp, 
       ename, 
       job, 
       mgr, 
       hiredate, 
       sal, 
       comm, 
       dept 
INTO   [hr_db].dbo.employee 
FROM   [hr_stage].dbo.employee;  

The table employee is created in hr_db database on development SQL Instance. This table is modeled after employee on hr_stage. The missing Where clause here copies the data along with the table structure.

To Another SQL Instance: Here you are asked to move employee table along with data from hr_db of dev_sql Instance to hr_db of prod_sql. This move includes the structure of the table including database constraints and the employee data.

SELECT empp, 
       ename, 
       job, 
       mgr, 
       hiredate, 
       sal, 
       comm, 
       dept 
INTO   [hr_db].dbo.employee 
FROM   [dev_sql_link].[hr_stage].dbo.employee ;  

 This SQL was executed on prod_sql SQL Instance. The production Instance connects to development Instance via a linked server (dev_sql_link). Creating a Linked Server is out of scope here but you can get the detail on Linked Server from my site.

The above SQL only moves the structure along with the data not the Constraints. Like I stated above, there are no SQL commands that can copy table's Constraints along with the table. You are required to manually create the database Constraints and Indexes. You may use SSMS to generate the DDL Constraints and Indexes. To make the move process is much faster, add Constraints and Indexes only after data move. Did you know why?

Data Move Only:

One Database to Another: Here the table is on both the databases. The data is stored on the employee table of hr_stage only. You are asked to copy data to employee table of hr_db database withing a dev_sql Instance. Assumptions: The  empty employee table exits on  hr_db database.

INSERT INTO [DBA-Maint].dbo.employee 
            (empno, 
             ename, 
             job, 
             mgr, 
             hiredate, 
             sal, 
             comm, 
             dept) 
SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate, 
       sal, 
       comm, 
       dept 
FROM   [dba_test].dbo.employee; 

 One Instance to Another: Here the table is on both the SQL Instances. The data is stored only in the employee table of dev_sql Instance. You are asked to copy data to employee table of  prod_sql  from dev_sql Instance. Assumption: The empty employee table exist in prod_sql under hr_db database.  

INSERT INTO [DBA-Maint].dbo.employee 
            (empno, 
             ename, 
             job, 
             mgr, 
             hiredate, 
             sal, 
             comm, 
             dept) 
SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate, 
       sal, 
       comm, 
       dept 
FROM   [dev_sql_link].[dba_test].dbo.employee; 

Anyone who uses Oracle as their DBMS, you can use CREATE TABLE AS SELECT (CTAS) to copy table and data from one schema to another or to a different Oracle Instance. To copy a table into different Instance, you will need to use db link to connect to a remote Instance. Oracles has a database link (db link) that is similar to that of SQL Linked Server.  To move just the data use "Insert into Select from" clause.

There are more than one ways to skin a cat, so are there more ways to move table and data using SSMS Export & Import Wizards,  data pump, or third party tools. Whatever I showed here is most effective  and  easy to implement moving strategies.