Select records from one server and insert in other?

Transact-SQL
1

Hi!

I have two identical databases (in different servers that don't communicate to each other) and I need to duplicate some records from one server to another. I need to select the records from one server and insert in the other, meaning that it has to be done in two steps and therefore the command would have to select the records and kind of to generate an insert script.

There is a way of doing it?

Thanks!

2

The simplest solution I can think of would be SSIS, or even Import/Export wizard. In SSMS, right-click on the target database, select Tasks -> Import Data. A wizard dialog will walk you through the steps. You can select whole tables or views, or you can write queries to select a subset of records from a table or view.

3

What is this SSIS or SSMS?

4

SSMS = SQL Server Management Studio
SSIS = SQL Server Integration Services

SSMS is the tool you use to access SQL Server and execute queries, manage databases, tables, views, etc...

SSIS is a service that runs on a server that executes packages. SSIS packages are created using Visual Studio - specifically SSDT (SQL Server Data Tools).

If this is a one-time operation - use the Import/Export utility within SSMS. This is a wizard that builds and executes an SSIS package. If this needs to be repeatable - then you can build the package in SSDT and deploy it to an integration services catalog.

If you do not want to use SSIS - you can use Powershell. In Powershell - you would get the results into a variable using Invoke-SqlCmd - then foreach over each item in the result variable to build an insert statement - then use Invoke-SqlCmd to execute the insert statement on the destination.

1 Like
5

Sorry about that @YanKleber ! Sometimes I forget that not everyone breathes, eats, and sleeps with SQL Server !!

Thanks @jeffw8713 for the explanations and for the Powershell approach.

1 Like
6

Thank you for the clarification, @jeffw8713! :+1:

It's alright, @JamesK! I am a coder mostly of time (not a DB admin) so my usage of SQL-Server is reduced to strictly the necessary. I am really not used to some terms. :grimacing:

7

hi another way is

Linked Servers ....

link both the servers ...
and then TSQL script to do the rest !!!!