Static Versus Dynamic Bulk Insert Imports of Multi-File Datasets

There are numerous use cases for multi-file imports of CSV files into a SQL Server table:

T‑SQL BULK INSERT vs OPENROWSET: CSV Imports, File Access Gotchas, and A PowerShell Script

Most SQL bulk insert and SQL Server openrowset tutorials skip file access issues that can stop imports cold. Both the bulk insert statement and openrowset function rely on the SQL Server service account to read a source file. The SQL Server service account must have read permission on the file or its folder. It is also convenient to have read & execute as well as list folder content permissions.

SQL Bulk Insert Command Examples

Learn about the SQL Server BULK INSERT command and how to use it to import text data in a SQL Server database table.

SQL Server Bulk Insert Row Terminator Issues

We often need to import data into SQL Server from a file. Sometimes unwanted end of line characters are part of the source file and these can create issues when importing the data. One of the ways to avoid this issue is to use SQL Server Integration Services (SSIS), but it doesn't always make sense to use SSIS for simple operations. Rahul Mehta explains that he uses BULK INSERT sometimes faces issues with Line Feeds and Carriage Returns. In this post, Rahul shows some examples and explains how to fix the issue.

Data import files

ADMINISTER BULK OPERATIONS

Dynamic SQL procedure for bulk insert

https://github.com/ktaranov/sqlserver-kit/blob/master/Stored_Procedure/usp_BulkUpload.sql

SQL-only ETL using a bulk insert into a temporary table (SQL Spackle)

This article demonstrates a method of importing a data file directly into a temporary table and selectively inserting data from the temporary table into a SQL Server table, all in a single set of SQL commands.

Error logging with Bulk Insert

Bulk Insert statement has an important feature of error logging which is rarely used.

ERRORFILE and MAXERRORS option with BULK INSERT

ERRORFILE and MAXERRORS option are rarely used important arguments in BULK INSERT so, I will try to highlight the use of these arguments in this script.