Copying data from one table to another using sql server import/export wizard.
Using SQL Server Import and Export Wizard we can import/export data from and to any supported data source.
Note : I am using sql server management studio 2017 here.
First right click on the database you want to export/import and click on Tasks
Then click on Import/Export Data
The wizard will open up. Click on next.
Now type in the server/database info of the source data and click next.
Now do the same for the destination table
For easy way of doing this (ie, if you just want to copy over all the data) simply choose the first option (Copy data from one or more tables or views)
Then choose the tables you want to copy data from and to.
Click on mappings to make sure the mappings are correct.
Note: here you also want to make sure you don’t have any identity columns mapped. That will cause an error when the wizard tries to insert data in to the new table where one of the mapped column is an identity column. (That’s what I’ve done below)
After that click next.
Then click finish.
*Make sure you don’t have any datatype mismatch.
**Also make sure you don’t have allow nulls in one column in the source table and not null in the mapped column of the destination column.