Tag: Data

SQL Server : Exporting and Importing Data

Import and Export Wizard

In SQL Server Management Studio this feature the SQL Server Integration Services functionality is called the “Import and Export Wizard”. Right-click on the database and select the “Tasks > Import Data” or “Tasks > Export Data” option. They both result in the same Wizard, but depending on which you pick different fields may be defaulted.

To transfer some data do the following.

  • Right-click on the database.
  • Select the “Tasks > Export Data” menu option.
  • Click next on the first page, so you move to the “Choose a Data Source” page.
  • Choose a source. This could be another database using any available provider, including databases on the local or remote machines, or even different database engines. It could even be a text file. In this case we will pick another database on the same server.
  • Make sure the following settings are correct.
    • Data Source : SQL Server Native Client 11.0
    • Server name : Your server name.
    • Authentication : Use Windows authentication, or set the SQL Server authentication credentials.
    • Database : Select the database you want to export from.
  • Click the “Next” button.
  • Choose a destination. This could be another database using any available provider, including databases on the local or remote machines, or even different database engines. It could even be a text file. In this case we will pick another database on the same server.
    • Data Source : SQL Server Native Client 11.0
    • Server name : Your server name.
    • Authentication : Use Windows authentication, or set the SQL Server authentication credentials.
    • Database : Select the database you want to import into.
  • Click the “Next” button.
  • Decide if you want to copy data from multiple tables or view, or use a single query. The later will allow you to write complex queries including joins of multiple tables, but it will result in a single object in the destination database. Here we will pick “Copy data from one or more tables or views”.
  • Click the “Next” button.
  • Chose the tables you want to copy. You can alter the destination schema using the “Edit Mappings” button. It also gives you some other options for how to deal with existing options. When you’ve completed your table selection and mappings, click the “Next” button.
  • Click the “Next” button to see the summary information.
  • If you are happy with the summary, click the “Finish” button.
  • Wait for the transfer to complete. You can see a summary of the operations and save a report if required.
  • Click the “Close” button.

Copy Database

You can copy a whole database using a wizard too.

  • Right-click on the database.
  • Select the “Tasks > Copy Database” menu option.
  • Select the source server and authentication method, then click the “Next” button.
  • Select the destination servers and authentication method, then click the “Next” button.
  • Decide on the transfer method. Using the detach and attach method is faster, but makes the source database offline. The SQL Management Option method is slower, but allows the database to remain online. Make your choice and click the “Next” button.
  • Select the database(s) and decide if this is a copy or move. Click the “Next” button.
  • Assign a destination database name, the location of the files and decide how to handle an existing database of the same name. Click the “Next” button.
  • Enter a package name, or accept the default one. Click the “Next” button.
  • Run immediately, or schedule for a later time. Click the “Next” button.
  • If you are happy with the summary information, click the “Finish” button.
  • If you chose to run immediately, wait for the operation to complete.
  • You can view or save a report. Once you are finished, click the “Close” button.

Bulk Insert

You can load data from a file using the SSIS Wizard described above. You can also load data from text from the command line using the following type of command.

BULK INSERT MyTable
  FROM 'C:\MyData.txt'
  WITH (
          FIELDTERMINIATOR = '|'
       )

Bulk Copy Program (BCP)

This utility can copy data from the database into a file, or copy data from files into the databases. You can get help using the “bcp /?” command from the command prompt or PowerShell prompt.

PS C:\> bcp /?
usage: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe {dbtable | query} {in | out | queryout | format} datafile
 [-m maxerrors] [-f formatfile] [-e errfile]
 [-F firstrow] [-L lastrow] [-b batchsize]
 [-n native type] [-c character type] [-w wide character type]
 [-N keep non-text native] [-V file format version] [-q quoted identifier]
 [-C code page specifier] [-t field terminator] [-r row terminator]
 [-i inputfile] [-o outfile] [-a packetsize]
 [-S server name] [-U username] [-P password]
 [-T trusted connection] [-v version] [-R regional enable]
 [-k keep null values] [-E keep identity values]
 [-h "load hints"] [-x generate xml format file]
 [-d database name]
PS C:\>

You can copy data out of the database using the following type of command, which uses a trusted connection (-T) to create a character file (-c) with a “|” delimiter (-t”|”) .

C:\>bcp MyDatabase.dbo.MyTable out c:\MyTable.txt -T -c -t"|"

You can import the same file with the following command. The parameters are similar, but we include a batch size (-b 1000).

C:\>bcp MyDatabase.dbo.MyTableAgain in c:\MyTable.txt -T -c -t"|" -b 10000

For more information see: