Users of SQL Server Database work with various database components including Table, Views, Stored Procedures, etc. In many cases, user may need to copy the SQL Server table schema and data from one database to another database in the same instance or different SQL instance. In the article, we will learn about different ways through which user can copy table from one database to another SQL Server.
Note: For this article, we will use Database1 as source and Database2 as destination database. The table that will be copied from Source to Destination is Department table under Computer Schema.
Let us learn about the different methods that will help user in copying the table from one database to another:-
Download Trial Version of SQL Server Database Migration Tool
The first method includes usage of SELECT INTO command for copying table from one database to another. The following syntax will be used:
SELECT * INTO DestinationDB.dbo.tablename from SourceDB.dbo.SourceTable
The statement will create the tables in the destination database first and then copy the data to these tables. However, if we want to copy objects, indexes, triggers, etc. we need to use the third method Generate Scripts.
Example:
“SELECT * INTO Database2. Computer.Department from Database1. Computer.Department”
The columns in destination table are created in the order defined in the select statement. All the columns in the table have the exact name and data type as in the source database. If we want to copy only the ID and name in table Department from Database1 to Database2, we will use:
“SELECT ID, Name into Database2.Computer.Department from Database1.Computer.Department”
Second method involves using SQL Server Export and Import Wizard, which is present in SQL Server Management Studio. The user can either export from the Source database or import from Destination database in order to copy the data.
Steps that need to be followed are:
This method provides an easy way to copy the tables from source database to destination database; however, it cannot be used to transfer the SQL Server Table’s indexes and keys.
The last method is done by generating scripts and it will help user in copying not only the table schema or data, but also allows user to copy views, functions, constraints, triggers, etc.
Steps to generate the scripts are as follows:
The method of generating scripts is useful in generating one single script for the table’s schema, data along with indexes and keys. This method cannot create the script in correct order in case there are relations between the tables.
The article has includes various methods to copy table from one database to another SQL Server Database. Each method has been explained in depth with steps to guide users in copying the table’s data easily. It further includes limitations that are associated with every copying method. However, if you do not want to go with these lengthy procedures then you can also try a handy third party SQL MDF repair tool to export table data from one database to another database in SQL Server.