1

I have two SQL Server instances, each with a database.

They are not on the same network and not connected to the Internet and the only way to get data between them is with a thumbdrive.

I want to export a table from one of the computers, put the exported data on a thumbdrive, then import the data into the other computer.

How do I do that?

The data has NULLS so I am afraid a flat file transfer will change the data. That is, a NULL is converted to an empty string.

The other solutions I have found require using linked servers or otherwise keeping the data in the databases.

In DB2 this is easy because I run a EXPORT command that exports to a ".idx" file and then IMPORT command that imports the data. This command also defines the table.

This data is only 23,000 rows.

migrated from stackoverflow.com Feb 9 '17 at 11:53

This question came from our site for professional and enthusiast programmers.

  • What is wrong with defining linked servers? What is the data volume you have to copy/replicate? – Alexei Feb 9 '17 at 5:56
  • Some methods: backup the source database. Restore this on the target, use insert/select to copy the data over. Or you can BCP.EXE to export and import 1 files per table. – Nick.McDermaid Feb 9 '17 at 6:16
1

Right Click DatabaseName -> Go to Task -> Generate Script One dialog is open

1). Select ALL Table & Click on Next

2). Click on Advance Select button and select value "Data only" in Type data to Script dropdown

3). And Click Finish

4). Copy result .sql file to target machine.

5). run resultant .sql file to target machine.

0

You could also take a look for a SQL export. It generates you a SQL file with insert statements. Then you don't have to think about NULL values. I did it multiple times for integration tests and other stuff.

If it is exported you could easily run the SQL file on the other server.

I used intellij idea for the export.

Another option is to do a dump of your data. You should be able to choose which tables should be dumped.

0

Here a way of doing it if your table as took much information (the SQL file size is superior to 40 Mb):

  1. EXPORT THE SCHEMA AND DATA IN A FILE

    • Use the ssms wizard Database >> Tasks >> generate Scripts… >> Choose the table >> choose db model and schema
    • Save the SQL file (can be huge)
    • Transfer the SQL file on the other server
  2. SPLIT THE DATA IN SEVERAL FILES

    • Use a program like textfilesplitter to split the file in smaller files and split in files of 10 000 lines (so each file is not too big)
    • Put all the files in the same folder, with nothing else
  3. IMPORT THE DATA IN THE SECOND SERVER

    • Create a .bat file in the same folder, name execFiles.bat
    • You may need to check the table schema to disable the identity in the first file, you can add that after the import in finished.
    • Use the tool: sqlcmd to import the data of each file. This will execute all the files in the folder against the server and the database with, the –f define the Unicode text encoding should be used to handle the accents:

      for %%G in (*.sql) do sqlcmd /S ServerName /d DatabaseName -E -i"%%G" -f 65001 pause

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.