sql transfer table with data to another server

SQL Server(2012) provides another way to generate script for the SQL Server databases with its objects and data. This script can be used to copy the tables’ schema and data from the source database to the destination one in our case.

1. Using the SQL Server Management Studio, right-click on the source database from the object explorer, then from Tasks choose Generate Scripts. 
2. In the Choose objects window, choose Select Specific Database Objects to specify the tables that you will generate script for, then choose the tables by ticking beside each one of it. Click Next. 
3. In the Set Scripting Options window, specify the path where you will save the generated script file, and click Advanced. 
4. From the appeared Advanced Scripting Options window, specify Schema and Data as Types of Data to Script. You can decide from here if you want to script the indexes and keys in your tables. Click OK. Getting back to the Advanced Scripting Options window, click Next.
5. Review the Summary window and click Next. 
6. You can monitor the progress from the Save or Publish Scripts window. If there is no error click Finish and you will find the script file in the specified path. 

SQL Scripting method is useful to generate one single script for the tables’ schema and data, including the indexes and keys. But again this method doesn’t generate the tables’ creation script in the correct order if there are relations between the tables.