Click here to Skip to main content
15,861,168 members
Articles / Programming Languages / SQL
Tip/Trick

Copy Table Schema and Data From One Database to Another Database in SQL Server

Rate me:
Please Sign up or sign in to vote.
4.91/5 (36 votes)
6 Oct 2013CPOL2 min read 1M   19   9
How to copy table and its data in SQL Server using Query as well as graphically

Introduction

Several times, we need to copy SQL Server table schema and data from one database to another database. In this tip, I show how to do this using a query as well as graphically in SQL Server.

I have created two databases named databasefrm and databaseto. In database databasefrm, I have a table named Emp containing data like ID, Name, Address and so on. I want to copy the table Emp to databaseto. We can do this using various methods.

Method 1: Using Query

The query required here is(Query Syntax):

SQL
Select * into DestinationDB.dbo.tableName from SourceDB.dbo.SourceTable 
Example
SQL
select * into databaseto.dbo.emp from databasefrm.dbo.Emp

This command only copies the table schema and data. If we want to copy objects, indexes, triggers or constraints, then we need to generate Scripts (third method) that we will talk about later in this article.

If we want to copy a table in the same database, then we need to first use our database, then execute this query:

SQL
select * into newtable from SourceTable
Example
SQL
select * into emp1 from emp

We can also select only a few columns into the destination table.

Query Syntax
SQL
select col1, col2 into <destination_table> from <source_table>
Example
SQL
select Id,Name into databaseto.dbo.emp1 from databasefrm.dbo.Emp 

Preview after executing this query:

image1

Here, we only copy Id and Name in table emp1 from the Emp table in the databasefrm database.

If we want to copy only the structure or the schema of the table, then we need to use this query:

SQL
select *into <destination_database.dbo.destination table> from _
<source_database.dbo.source table> where 1 = 2

Preview after executing this query:

Image 2

Method 2

  1. Open SQL Server Management Studio.
  2. Right-click on the database name, then select "Tasks" > "Export data..." from the object explorer.

    Image 3

  3. The SQL Server Import/Export wizard opens; click on "Next".
  4. Image 4

  5. Provide authentication and select the source from which you want to copy the data; click "Next".

    Image 5

  6. Specify where to copy the data to; click on "Next".

    Image 6

    Image 7

    Image 8

    Image 9

Method 3: Generate Script

By using the above two methods, we are only able to copy the table schema and data but we are not able to copy views, functions, constraints, triggers and so on. We can copy all these things by generating scripts.

Let's see how to generate a script:

  1. Right-click on the database name, then select "Tasks" then click on "Generate Scripts". After that, the Script Wizard opens. Click on "Next".
  2. Select the Database you want to script.
  3. Choose the object types. Click on "Next".
  4. Select the tables.
  5. Select the Output option for the script.
  6. Change/Edit the database name to the name you want to execute this script for.

Let's see how to do it step-by-step:

generate script

That's all. Hope you like it.

Thanks!

My Other Tips and Tricks

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Student
India India

Comments and Discussions

 
Questiondata connection Pin
Member 1453777222-Jul-19 2:12
Member 1453777222-Jul-19 2:12 
QuestionBrilliant Article but can it be automated? Pin
OldGooner18-Mar-18 22:18
OldGooner18-Mar-18 22:18 
GeneralMy vote of 5 Pin
Member 1345203125-Oct-17 6:43
Member 1345203125-Oct-17 6:43 
QuestionThanks!! Pin
estebanslipknot8-Nov-16 5:06
estebanslipknot8-Nov-16 5:06 
GeneralRe: Thanks!! Pin
Anoop Kr Sharma25-Nov-16 22:46
professionalAnoop Kr Sharma25-Nov-16 22:46 
Questionin which place to use the query Pin
diana_j11-Nov-15 1:43
diana_j11-Nov-15 1:43 
AnswerRe: in which place to use the query Pin
MarcusCole68331-Feb-16 10:55
professionalMarcusCole68331-Feb-16 10:55 
Questionhow can i auto copy data from http://softwaresb.com/ Pin
Member 120373946-Oct-15 5:43
Member 120373946-Oct-15 5:43 
BugFormats and default values not applying Pin
Member 935003914-Sep-15 23:39
Member 935003914-Sep-15 23:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.