Sql-server – Copy DB table across 2 different MSSQL servers

sqlsql server

I have 2 MSSQL servers running, with more or less the same data set. It is really a production/development setup.

Now, my workflow is like this. Every day backups are made of each one of the databases on the production server. I transfer the backups over the Internet (10s of Gigabytes) to the development server. I "restore" the database on the development server using the backup from the production server, so that I have a current data set.

My question is this:

Is there an easy and quick way to "export" a single table from a MSSQL server database and import it in a database in a different MSSQL server?

I am interested more in a "best practice" kind of operation, than some jury-rigged POS trick.

UPDATE

After some Googling around I found a function of the MSSQL Server that produces a script that exports the table and imports it when run. I cannot use this since I need to import the table in another Server.

Best Answer

You can also fetch the data direct per SQL on your target Server. If you have network access:

SELECT a.*
INTO <YOUR TARGET TABLE>
FROM OPENROWSET('SQLNCLI', 'Server=<IP/SERVERNAME>;Trusted_Connection=yes;',
     'SELECT *
      FROM <YOUR TABLE>
      ') AS a;

But you must create the indexes by your self.

Or you using INSERT:

INSERT INTO 
<YOUR TARGET TABLE>
FROM OPENROWSET('SQLNCLI', 'Server=<IP/SERVERNAME>;Trusted_Connection=yes;',
         'SELECT *
          FROM <YOUR TABLE>
          ') AS a;
Related Topic