1.BCP and Bulk Insert

1.1 What are Bulk Operations?

• Bulk operations are statements in which you move and insert a large amount of data
• No requirement for being “bulk”, just summed up as a lot
• Bulk operations can move data in and out of SQL or other platforms

1.2 Bulk Copy Program

• BCP uses the CMD of the system, so it is command line driven

• Since it uses the CMD, it is avoiding any GUI or software, making it the fastest way for communication between DBMS and RDBMS

• BCP allows you to either pull data from SQL server or move data into SQL server from system files

• Operation type OUT/IN/QUERYOUT/FORMAT

• OUT, IN, FORMAT options needs a table or view

• QUERYOUT option needs a SQL query and it has to use 3 part naming convention

bulk

BCP "SELECT TOP 100 BusinessEntityID, FirstName, LastName FROM AdventureWorks2017.Person.Person" QUERYOUT C:\cmd\Per.txt -c -T

1.1.2 操作符号 都要在一行里面写出来！！！！！

1.1.2.1 Data to a file from a table using OUT option

-T Trusted connection
-c Character data type for the output file

BCP AdventureWorks2012.HumanResources.Employee OUT C:\BulkOps\Emp.txt -c -T 

BCP "SELECT TOP 100 BusinessEntityID, FirstName, LastName 
FROM AdventureWorks2012.Person.Person" QUERYOUT C:\BulkOps\Per.txt -c -T -t,

BCP AdventureWorks2012.dbo.Per IN C:\Raj\BulkOps\Per.txt -c -T -t,

1.1.2.2 Creating a comma separated file using OUT option

-t specifying there is a column terminator or delimiter followed by delimiter value

-t, --in this case -t specifies there is a delimiter for column and (,) is the delimiter

BCP AdventureWorks2012.HumanResources.Employee OUT C:\OPFiles\Emp.txt -c -T -t, 

1.1.2.3 Creating a file using OUT option and error out put to another file

-e specifies that there is an error file followed by path for error file

BCP AdventureWorks2012.HumanResources.Employee OUT 
C:\OPFiles\Emp.txt -c -T -e C:\OPFiles\ErrorFile.txt

1.1.2.4 Creating a file using QUERYOUT option

BCP SELECT top 1000 FirstName, LastName from 
AdventureWorks2012.Person.Person QUERYOUT C:\BulkOps\Person.txt -c -T

others:

--File Location to be copied
--Data format -c/-N/-n/-w (-c Character, -N Unicode Native, -n Native, -w Unicode)
--Field Terminator -t
--Server info -S
--Login info -T/-U
--Hints ORDER, FIRETRIGGERS
--Ignore Identity Column values -E
--Batch Size -b value
--First Row start -F value
--Last Row -L value
--Error File -e File Path

1.3 Bulk Insert

• Bulk Insert is executed in SQL server and used in a query
• Can only INSERT data into SQL, so only one direction with bulk data
• Since being used in queries, it can be logged, use transactions, etc
• Slower than Bulk Copy Program

bulk insert bulk_op
from 'c:\bulk_op\empinfo.txt'

1.4 Differences Between BCP & BI

diff

1.4.1 bulk insert

• SQL Server Query
• Import data only
• Can be logged
• Can be used with transactions and error handling
• Slower due to use of software

1.4.2 bcp

• Command Line Utility for the system, CMD
• Can import and export data from SQL server to system files
• No direct option for performing error handling, logging, or transactions
• Faster of the two

1.5 note

BCP AdventureWorks2012.HumanResources.Employee IN C:\Users\admin\Desktop\BulkOps\Employee.dat -c -t, -S localhost -T -h FIRE_TRIGGERS -E -b 100 -F 25 -e C:\Users\admin\Desktop\BulkOps\ErrorFile.txt

BCP AdventureWorks2012.dbo.Emp IN C:\Users\admin\Desktop\BulkOps\Employee.dat -c -t, -S localhost -T -h FIRE_TRIGGERS

BCP "SELECT P.BusinessEntityID, P.FirstName, P.LastName, E.BirthDate, E.MaritalStatus, E.Gender FROM AdventureWorks2012.Person.Person P JOIN AdventureWorks2012.HumanResources.Employee E ON E.BusinessEntityID = P.BusinessEntityID" QUERYOUT C:\Users\admin\Desktop\BulkOps\Emp_QO.txt -N -t, -T

BCP AdventureWorks2012.Person.Person OUT C:\Users\admin\Desktop\BulkOps\Employee.dat -c -t, -S localhost -T -h ORDER(FirstName)

--CSV, XLT, DAT, FMT, TXT