Task: Archive table Data or move table data from one database to another database.
Solution: We can do this is in couple of ways. You can do it all manually or using SQL scripts. I will list some of the steps that I have recently done to archive more than 900K+ records in a single table.
Here’s the current scenario:
- Both the source and destination databases are on the same sql server.
- The sql server version is MS SQL Server 2008 R2
- Source Database and Destination Database
Open Microsoft SQL Server Management Sudio 18 ( or what ever version of MS SQL server Management sudio you have installed)
And then connect to your SQL server.
Next Step: (You can skip this step if you have already got the destination database and table.)
Create the destination database if it does not exist. Since you are archiving the data, you may consider changing the revovery model to simple. (Default is full).
Right click on the Source table and Script Table as –> Drop and Create
Now, change the database to the destination database in the top menu’s area and execute the sql. This script would drop the table if it already exists and create a new table with exact same columns as source database.
Manual : Using SQL Server Export/Import wizard
Next steps is the main step, where you would import the data from one table to another. In this step we are talking of using the SQL server’s built in Export Import wizard.
Using Scripts:
—-To be continued.
