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.
Download SQL Server Database Migration Tool For Free
Download Now Purchase Now
Basic Steps to Copy Table From One Database to Another Database in SQL Server Using The Software (Quite Easy to Run):
Step-1: Click on ‘Open’ to load SQL file containing the desired table. Select Online Mode after downloading the software. Provide all the necessary authentication details.
Step-2: In the next window, you can view all the SQL objects and records conatining in that file, Click ‘Export’.
Step-3: Select ‘SQL Server Database’ option and provide the authentication details (as it is for destination server). You can create a new database. You also have the option to export data with only schema or schema and records as well. Finally, click on Export.
Using SELECT INTO Query
Note: To copy table from one database to another 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.
The first method includes usage of SELECT INTO command for copying table from one database to another. The following syntax will be used:
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. This way users can copy data from one table to another SQL server with ease.
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:
Using Export/Import Wizard
The second method to copy table data from one database to another involves 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.
Also Read: Migrate SQL Server Database to Lower Version of SQL Server
Using Generate Scripts
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.
Also Read: Export SQL Server Database to SQL File Format
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 tools to export table data from one database to another database in SQL Server.
Now, let’s quickly have a look at the user reviews that already used this automated solution to get rid of their issue for the same.
How to copy all data from one table to another table in SQL Server?
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
How do I copy data from one table to another server?
Launch SQL Server Management Studio. Select and right-click on the Source Database, go to Tasks > Export Data. Import/Export Wizard will be opened and click on Next to proceed. Enter the data source, server name and select the authentication method and the source database.
What is fastest way to copy a data from 1 table to another table?
Using a SQL multi row fetch is the fastest way to copy data from one table to another (I was surprised how much faster it is when compared to CPYF). Using VARCHAR is slower than just a straight CHAR (not surprised).