Search code examples
sqlsql-serverdatabasessms

Copy a subset of data from remote database to local SQL Server

I have a remote database that I want to copy on my local SQL Server.

IMPORTANT: I only want a sample data (1k rows for instance) from each table, and there are about 130 different tables.

I have tried to use the export data procedure in SSMS. Put simply, I go to TASKS> EXPORT DATA > CHOSE SOURCE (the remote db)> CHOSE DESTINATION (my local db) > CHOSE THE TABLES TO COPY > COPY

What I have tried:

I've tried to write down in this tool the SQL query like

SELECT TOP 1000 * 
FROM TABLE1 GO ... 

SELECT TOP 1000 * 
FROM TABLE130

But on the mapping step, it puts every result within a single table instead of creating the 130 different output tables.

FYI, the above procedure is taking 2min for one table. Doing it one by one for each table will take 130 *2min = 4hours and half... plus it is so boring

Do you have any idea for resolving this situation?

Thank you regards

Solution

  • If you only want a subset you are going to have problems with foreign keys, if there are any in your database.

    Possible approaches to extract all data or a subset

    • Use SSIS to extract the data from the source db and load into your local db
    • Write a custom application that does the job. (You can use SQL Bulk Copy)

    If you purely wan't to do do it in SSMS you can create a linked server on your local server to the remote server.

    This way you can do something like this if the tables or not yet created on your local server:

    SELECT TOP 1000 *
INSERT INTO [dbo].Table1
FROM [yourLinkedServer].[dbo].[Table1]

    Changing the INTO table and FROM table for each table you want to copy.