Moving data between SQL servers should be simple, however there are times when it is anything but.
If you have ever tried exporting data to a flat file, then reimporting it to another server you will know that it seldom works first time.
There is another way though, which involves generating scripts – you can actually script out the data as insert statements on one server, then just hit execute and run on the other.
How To Export A Table Including The Data Using SSMS
- Right click on the database that contains the table you wish to export and select Tasks | Generate Scripts
- Select Next then Selecct specific database objects and choose the table that you wish to export
- Select Next here is where you can chose to export it to a file, or a new query window. Then click Advanced
- Scroll down to the option called Types of data to script and choose Schema and Data (or just data if the table already exists at the destination)
- Click Next and review your options then click Next again and the script will be created
This way of exporting and importing data is not exactly intuitive, but once you have done it a couple of times you will find it easier than other methods.
Note: When generating scripts for a table, by default not all the indexes will be included in the schema script – you might want to change this advanced option as well.
Do you have any ways you prefer to import and export data? Let us know in the comments below.