One of the tables I wanted to copy to my local SQL server has nearly 200 million entries. It would take far too long to copy if I generate scrips for the data using the SQL server scripting tool. Besides, I didn’t need all 200 million entries for development anyway.
The Linked Servers feature in SQL server management studio makes it simple to copy a selection of data from table to another. Once two database instances are linked, you can use SQL select and insert commands to copy data.
Create a linked server
Open the local database in SQL server management studio. Select Server Objects -> Linked Servers. Then right click and select New Linked Server. The following dialogue will appear on screen.
Add an appropriate name in the Linked Server text box (for example, lotsofdata-server). Under server type, select the SQL server radio button. Choose Security on the left navigation pane. Select the radio button Be made using this security context. Enter the correct username and password for the remote server.
To copy the first 10000 rows from the remote server lotsofdata-server into a table that doesn’t exist in the local database
select TOP 10000 * into dbo.[awesomeTable] from [lotsofdata-server].[awesomeDatabase].dbo.[awesomeTable]
If the table already exist in the local database,
insert into dbo.[awesomeTable] select TOP 10000 * from [lotsofdata-server].[awesomeDatabase].dbo.[awesomeTable]
Copying an identity column
If the table you wanted to copy contains an identity column, then you need to turn on identity insert
SET IDENTITY_INSERT awesomeTable ON
and explicitly specify all the columns you are inserting into the table, like
insert into dbo.[awesomeTable] (col1, col2) select TOP 10000 (col1, col2) from [lotsofdata-server].[a-database-name].dbo.[awesomeTable]
Otherwise, SQL server will complain along the lines of cannot insert explicit value for identity column in table awesomeTable.