0

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.

  • What percentage, roughly, of that entire database, or better, that one table you are after, changes on a night per night basis? You are now transferring everything, but if only a part changes, you could export the table only on both the production and dev machine, then use something like rsync to transfer across only the differences. Then import that. – JayMcTee Jun 18 '15 at 12:06
  • @JayMcTee This is indeed my issue. I want to only export/transfer/import a single table instead of a full DB backup file. My problem is, I do not know how to export/import a single table. – dsljanus Jun 18 '15 at 12:12
1

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;
1

The export part has been answered over at DBA:

https://dba.stackexchange.com/questions/25755/taking-a-backup-of-a-single-table-in-the-sql-server

How to export one single table on SQL server has been answered numerous times on Stackoverflow too:

https://stackoverflow.com/questions/680552/table-level-backup

https://stackoverflow.com/questions/19698310/backup-a-single-table-with-its-data-from-a-database-in-sql-server-2008

Google yields another many ways:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/923d1a5a-2f21-4193-9513-ab944145a58f/exporting-a-single-table-from-a-database

Once you have that export of a single table, transfer that by any means (rsync for example, or SFTP, scp) and then import it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.