Lesson 39. BULK INSERT

There are numerous ways to get data into SQL Server. One of the fastest and least clunky-to-set-up ways is by using the BULK INSERT statement to ingest large CSVs.

When you use BULK INSERT, the table that you are importing data into has to match the schema of the file you are importing. I teach you how to work around this limitation in the lesson titled Large CSVs Into Data Warehouse Staging Tables.

BULK INSERT Syntax

In [ ]:

BULK INSERT YourSchema.YourTable
FROM 'FullyQualifiedFilePath\YourFile.csv'

Examples

Basic Bulk Insert Example

In this example, we will use the "Customer Signature for Churn Analysis" dataset. Not every dataset you import is going to be clean and perfect. Sometimes you have to give instructions to BULK INSERT. In this case, we have to specify how the columns are separated in the file, and we need to tell it to skip the header row.

In [ ]:

USE demo

DROP TABLE IF EXISTS CustomerSignature

CREATE TABLE CustomerSignature(
recordID NVARCHAR(255) NULL,
state NVARCHAR(255) NULL,
account_length NVARCHAR(255) NULL,
area_code NVARCHAR(255) NULL,
international_plan NVARCHAR(255) NULL,
voice_mail_plan NVARCHAR(255) NULL,
number_vmail_messages NVARCHAR(255) NULL,
total_day_minutes NVARCHAR(255) NULL,
total_day_calls NVARCHAR(255) NULL,
total_day_charge NVARCHAR(255) NULL,
total_eve_minutes NVARCHAR(255) NULL,
total_eve_calls NVARCHAR(255) NULL,
total_eve_charge NVARCHAR(255) NULL,
total_night_minutes NVARCHAR(255) NULL,
total_night_calls NVARCHAR(255) NULL,
total_night_charge NVARCHAR(255) NULL,
total_intl_minutes NVARCHAR(255) NULL,
total_intl_calls NVARCHAR(255) NULL,
total_intl_charge NVARCHAR(255) NULL,
number_customer_service_calls NVARCHAR(255) NULL,
churn NVARCHAR(255) NULL,
customer_id NVARCHAR(255) NULL
)

BULK INSERT CustomerSignature
FROM 'E:\customer_data_edited.csv'
WITH (
FIELDTERMINATOR = ',',
FIRSTROW = 2
);

SELECT * FROM CustomerSignature

DROP TABLE CustomerSignature