Bulk Import and Export Options in SQL Server

1.   bcp
  • Command line, thus it can be used as a batch file
  • Import or export
  • Can also be used to create a format file based on the table data in xml or non-xml format
  • Ideal for simple import/export tasks
  • Syntax

 bcp [database_name.] schema.{table_name | view_name | "query" {in data_file | out data_file | queryout data_file | format nul}
  [-a packet_size]
  [-b batch_size]
  [-c]
  [-C { ACP | OEM | RAW | code_page } ]
  [-d database_name]
  [-e err_file]
  [-E]
  [-f format_file]
  [-F first_row]
  [-h"hint [,...n]"]
  [-i input_file]
  [-k]
  [-K application_intent]
  [-L last_row]
  [-m max_errors]
  [-n]
  [-N]
  [-o output_file]
  [-P password]
  [-q]
  [-r row_term]
  [-R]
  [-S [server_name[\instance_name]]
  [-t field_term]
  [-T]
  [-U login_id]
  [-v]
  [-V (80 | 90 | 100 )]
  [-w]
  [-x]
  /?

  • Examples
A.     Copying table rows into a data file (with a trusted connection)

      bcp AdventureWorks2012.Sales.Currency out Currency.dat -T -c

B.      Copying table rows into a data file (with Mixed-mode Authentication)

 --The system will prompt you for your password.

bcp AdventureWorks2012.Sales.Currency out Currency.dat -c -U<login_id> -S<server_name\instance_name>

 C.     Copying data from a file to a table

To create the empty table, in Query Editor, enter the following command:

USE AdventureWorks2012;
GO

SELECT * INTO AdventureWorks2012.Sales.Currency2
FROM AdventureWorks2012.Sales.Currency WHERE 1=2;

To bulk copy the character data into the new table,

bcp AdventureWorks2012.Sales.Currency2 in Currency.dat -T -c

D.     Copying a specific column into a data file

bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout Currency.Name.dat -T -c

E.      Copying a specific row into a data file

bcp "SELECT * FROM AdventureWorks2012.Person.Person WHERE FirstName='Jarrod' AND LastName='Rana' "  queryout "Jarrod Rana.dat" -T -c

F.      Copying data from a query to a data file

bcp "SELECT FirstName, LastName FROM AdventureWorks2012.Person.Person ORDER BY LastName, Firstname" queryout Contacts.txt -c -T

G.     Creating a non-XML format file

-- You must use “format nul” for a format file
bcp AdventureWorks2012.Sales.Currency format nul -T -c  -f Currency.fmt

H.     Creating an XML format file

bcp AdventureWorks2012.Sales.Currency format nul -T -c -x -f Currency.xml

I. Using a format file to bulk import with bcp

    bcp AdventureWorks2012.Sales.Currency2 in Currency.dat -T -f Currency.xml

2.   Bulk Insert
  • Provide the same functionality as bcp import, but run as T-QL script in SSMS or other programs
  • Imports only
  • For the same task, Bulk Insert performs better than bcp import
  • The same purpose, but different syntax

BULK INSERT
  [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]       FROM 'data_file'
    [ WITH
   (
  [ [ , ] BATCHSIZE = batch_size ]
  [ [ , ] CHECK_CONSTRAINTS ]
  [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
  [ [ , ] DATAFILETYPE =
     { 'char' | 'native'| 'widechar' | 'widenative' } ]
  [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
  [ [ , ] FIRSTROW = first_row ]
  [ [ , ] FIRE_TRIGGERS ]
  [ [ , ] FORMATFILE = 'format_file_path' ]
  [ [ , ] KEEPIDENTITY ]
  [ [ , ] KEEPNULLS ]
  [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
  [ [ , ] LASTROW = last_row ]
  [ [ , ] MAXERRORS = max_errors ]
  [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
  [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
  [ [ , ] ROWTERMINATOR = 'row_terminator' ]
  [ [ , ] TABLOCK ]
  [ [ , ] ERRORFILE = 'file_name' ]
)]

  • EXAMPLES

A.     Using pipes to import data from a file

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR =' |',
ROWTERMINATOR =' |\n'
);

B.      Using the FIRE_TRIGGERS argument

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR =' |',
ROWTERMINATOR = ':\n',
FIRE_TRIGGERS
);

C.     Using line feed as a row terminator

The following example imports a file that uses the line feed as a row terminator such as a UNIX output:

DECLARE @bulk_cmd VARCHAR(1000);
SET @bulk_cmd = 'BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>''
WITH (ROWTERMINATOR = ''' + CHAR(10) + ''')';
EXEC(@bulk_cmd);

D.     Importing a numeric value that uses scientific notation by using a format file

This example uses the following table:

CREATE TABLE t_float(c1 float, c2 decimal (5,4))

The user wants to bulk import data into the t_float table. The data file, C:\t_float-c.dat, contains scientific notation float data; for example:

8.0000000000000002E-2    8.0000000000000002E-2

However, BULK INSERT cannot import this data directly into t_float, because its second column, c2, uses the decimal data type. Therefore, a format file is necessary. The format file must map the scientific notation float data to the decimal format of column c2.

The following format file uses the SQLFLT8 data type to map the second data field to the second column:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/> </RECORD> <ROW>
<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLFLT8"/> </ROW> </BCPFORMAT>

To use this format file (using the file name C:\t_floatformat-c-xml.xml) to import the test data into the test table, issue the following Transact-SQL statement:

BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');
GO

3.   OPENROWSET and OPENDATASOURCE
  • For importing a remote data source into a local table
  • The data is at a remote source
  • Both appear as functions with no input variables.
  • Both depend on the existence of OLE DB providers, and we can access the data
  • OPENROWSET returns a table, and is often used in the place where a table is expected such as the FROM clause
  • OPENDATASOURCE is used as the server part of the four-part-name when a linked server is not feasible.
  • OPENROWSET  Syntax

OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
  | 'provider_string' }
  , {   [ catalog. ] [ schema. ] object
      | 'query'
    }
  | BULK 'data_file' ,
      { FORMATFILE = 'format_file_path' [ <bulk_options> ]
      | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
<bulk_options> ::=
  [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
  [ , ERRORFILE = 'file_name' ]
  [ , FIRSTROW = first_row ]
  [ , LASTROW = last_row ]
  [ , MAXERRORS = maximum_errors ]
  [ , ROWS_PER_BATCH = rows_per_batch ]
  [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ]

  • OPENROWSET Examples

o   Using OPENROWSET with SELECT and the SQL Server Native Client OLE DB Provider

The following example uses the SQL Server Native Client OLE DB provider to access the HumanResources.Department table in the AdventureWorks2012 database on the remote serverSeattle1. (Use SQLNCLI and SQL Server will redirect to the latest version of SQL Server Native Client OLE DB Provider.) A SELECT statement is used to define the row set returned. The provider string contains the Server and Trusted_Connection keywords. These keywords are recognized by the SQL Server Native Client OLE DB provider.

SELECT  a.*
FROM    OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
                  'SELECT GroupName, Name, DepartmentID
     FROM AdventureWorks2012.HumanResources.Department
     ORDER BY GroupName, Name') AS a;

o   Using the Microsoft OLE DB Provider for Jet

The following example accesses the Customers table in the Microsoft
Access Northwind database through the Microsoft OLE DB Provider for Jet.

Note: This example assumes that Access is installed. To run this example, you must install the Northwind database.

SELECT  CustomerID ,
       CompanyName
FROM    OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft   Office\OFFICE11\SAMPLES\Northwind.mdb'; 'admin'; '', Customers);
GO

o   Using OPENROWSET and another table in an INNER JOIN

USE Northwind;
GO
SELECT  c.* ,
       o.*
FROM    Northwind.dbo.Customers AS c
       INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                             'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb'; 'admin'; '',
                           Orders) AS o ON c.CustomerID = o.CustomerID;
GO

o   Using OPENROWSET to bulk insert file data into a varbinary(max) column

USE AdventureWorks2012;
GO
CREATE TABLE myTable
   (
     FileName NVARCHAR(60) ,
     FileType NVARCHAR(60) ,
     Document VARBINARY(MAX)
   );
GO

INSERT  INTO myTable
       ( FileName ,
         FileType ,
         Document
       )
SELECT  'Text1.txt' AS FileName ,
        '.txt' AS FileType,
        *
FROM    OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;

o   Using the OPENROWSET BULK provider with a format file to retrieve rows from a text file

The following example uses a format file to retrieve rows from a tab-delimited text file, values.txt that contains the following data:

1     Data Item 1
2     Data Item 2
3     Data Item 3

The format file, values.fmt, describes the columns in values.txt:

9.0
2
1  SQLCHAR  0  10 "\t"        1  ID                SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"      2  Description       SQL_Latin1_General_Cp437_BIN

This is the query that retrieves that data:

SELECT  a.*
FROM    OPENROWSET(BULK 'c:\test\values.txt',
                  FORMATFILE = 'c:\test\values.fmt') AS a;

  • OPENDATASOURCE  Syntax

OPENDATASOURCE ( provider_name, init_string )

  • OPENDATASOURCE  EXAMPES

--creates an ad hoc connection to the Payroll instance of SQL Server on server
--London,and queries the AdventureWorks2012.HumanResources.Employee table.

SELECT  *
FROM    OPENDATASOURCE('SQLNCLI',
                      'Data Source=London\Payroll;Integrated Security=SSPI')
   .AdventureWorks2012.HumanResources.Employee

--creates an ad hoc connection to an Excel spreadsheet in the 1997 - 2003 format.
SELECT  *
FROM    OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
                      'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$];

4.   The Data Import and Export Wizard
  • The SQL Server Import and Export Wizard offers the simplest method to create an Integration Services package that copies data from a source to a destination.
  • Some data sources, such as Access or Excel, only have a 32-bit provider available. To work with these data sources, you might have to install and run the 32-bit version of the wizard.
  • You can start the SQL Server Import and Export Wizard from the Start menu, from SQL Server Management Studio, from SQL Server Data Tools (SSDT), or at the command prompt.
  • The SQL Server Import and Export Wizard can copy data to and from any data source for which a managed .NET Framework data provider or a native OLE DB provider is available (i.e., could be local or remote). The list of available providers includes the following data sources:
    • SQL Server
    • Flat files
    • Microsoft Office Access
    • Microsoft Office Excel


5.   SSIS
  • For complex ETLs