Bulk Copy in SQL Server

• Got bulk data copying to do? SQL Server can do it for you nicely. The SQL Server bulk copy feature supports the transfer of large amounts of data into or out of a SQL Server table or view.
• SQL Server supports exporting data in bulk (bulk data) from a SQL Server table and
  importing bulk data into a SQL Server table or nonpartitioned view.
• Bulk exporting refers to copying data from a SQL Server table to a data file.
• Bulk importing refers to loading data from a data file into a SQL Server table.
  For example, you can export data from a Microsoft Excel application to a data file and then bulk import that data into a SQL Server table.
• The data can be moved between SQL Server and an operating-system data file, such as an ASCII file.
• The data source/target in SQL Server can be Table, View or a ResultSet of a T-SQL statement. 
  The data source/target in Operating System can also be a file (csv, excel, etc).
• Programmers coding applications to use the bulk copy functions should follow the general rules for good bulk copy performance. See Bulk Import and Export of Data (SQL Server). 
• The following basic methods are available for bulk data copy:
• bcp utility : is a command-line utility by name bcp.exe for bulk import/export. 
• By default, all the rows in the data file are imported as one batch. 
• To distribute the rows among multiple batches, specify a batch_size that is smaller than the number of rows in the data file. 
• If the transaction for any batch fails, only insertions from that batch are rolled back.
• BULK INSERT statement : is a T-SQL statement that imports data directly from a data file into a database table or nonpartitioned view. Here, the data file is read by the SQL Server process.
• INSERT ... SELECT * FROM OPENROWSET(BULK...) statement : is a T-SQL statement that uses the OPENROWSET bulk rowset provider to bulk import data into a SQL Server table by specifying the OPENROWSET(BULK...) function to select data in an INSERT statement.
• SQL Server Import and Export Wizard : creates simple packages that import/export data between many popular data formats including databases, spreadsheets, and text files.
• Some example snippets with various bulk copy methods:
• Prerequisites for Minimal Logging in Bulk Import : 
  
• For a database under the full recovery model, all row-insert operations that are performed by bulk import are fully logged in the transaction log. Warning: Large data imports can cause the transaction log to fill rapidly if the full recovery model is used. 
  In contrast, under the simple recovery model or bulk-logged recovery model, minimal logging of bulk-import operations reduces the possibility that a bulk-import operation will fill the log space. Minimal logging is also more efficient than full logging.
• Note: The bulk-logged recovery model is designed to temporarily replace the full recovery model during large bulk operations.
• Although data insertions are not logged in the transaction log during a minimally logged bulk-import operation, the Database Engine still logs extent allocations each time a new extent is allocated to the table.
• When transactional replication is enabled, BULK INSERT operations are fully logged even under the Bulk Logged recovery model.
• Minimal logging requires that the target table meets the following conditions:
• The table is not being replicated.
• Table locking is specified (using TABLOCK).
• Table is not a memory-optimized table.
• If the table has no indexes, data pages are minimally logged.
• If the table has a clustered index and is empty, both data and index pages are minimally logged. 
• If the table has no clustered index but has one or more non-clustered indexes, data pages are always minimally logged.
• If the table is empty, index pages are minimally logged.
  If table is non-empty, index pages are fully logged. 
• If a table has a btree based clustered index and is non-empty, data pages and index pages are both fully logged regardless of the recovery model.
• Use the following guidelines when you bulk import data from a data file to an instance of Microsoft SQL Server:
• Obtain required permissions for your user account.
• Use the bulk-logged recovery model.
• Back up after bulk importing data.
  For the bulk-logged recovery model or full recovery model, a log backup is enough.
• ProTip: Drop table indexes to improve performance for large bulk imports.
  This guideline is when "import data" is way larger than "data already in the table". Otherwise, dropping the indexes is counterproductive; the time required to rebuild the indexes might be longer than the time saved during the bulk-import operation.
• Find and remove hidden characters in the data file.