Copy 10k rows from table to table on other server

Question

I cannot use linked server.

Both databases on both servers have the same structure but different data.

I have 10k rows to transfer from the DB on one server to the same DB on the other. I cannot restore the DB on the other server as it will take a huge amount of space that I don't have on the other server.

So, I have 2 options that I don't know how to execute:

1. Backup and restoring only one table - the table is linked to many other tables but these other tables exist on the other server too. Can I somehow delete or remove the other tables or make a backup only over one table?
2. I need to transfer 10k rows. How is it possible to create 10k insert queries based on selected 10k rows?

Answer 1

Can I somehow delete or remove the other tables or make a backup only over one table?

No you can not do this, unfortunately

How is it possible to create 10k insert queries based on selected 10k rows?

Right-click on Database -> Tasks -> Generate scripts -> (Introduction) Next

Chose Select specific database objects -> Tables, chose table you need -> Next

Advanced -> Search for Types of data script change from Schema only (by default) to Data only -> OK

Chose where to save -> Next -> Next. Wait the operation to end.

This will generate the file with 10k inserts.

Another way is to use Import/Export wizard (the most simple way for one-time-import/export) if you have link between databases.

Answer 2

There are many ways to choose from, here is one way using BCP. That's a tool that ships with SQL Server to Import and Export Bulk Data.

The outlines of the process:

1. Export the data from the source server to a file using BCP - BCP OUT for a whole table, or BCP QUERYOUT with a query to select the 10k rows you want exported
2. Copy the file to the destination server
3. Import the data using BCP on the destination database - BCP IN.

Answer 3

My suggestion would be to export these rows to excel( you can do this by copy pasting your query output) and transfer this to other server and import it there.

this is the official method :- https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql

and this is the the unofficial method : http://therealdanvega.com/blog/2010/08/04/create-sql-insert-statements-from-a-spreadsheet.

Here I have assumed that you only need to transfer the transactional data and your reference data is same on both server. So you will need to execute only one query for exporting your data