BULK INSERT

« 1|2|3 »

Recently, there was a post where the user found himself in a problem when loading data utilizing BULK INSERT
This post is the first from a series, wich I want to explain how to use BULK INSERT in multiple possible ways.

BULK INSERT - as mentioned in BOL, BULK INSERT "Imports a data file into a database table or view in a user-specified format in SQL Server 2008 R2. Use this statement to efficiently transfer data between SQL Server and heterogeneous data sources."

Notice the reference "table or view". I will cover this one in my next post.

In other words, you can insert a text file or a flat file into a SQL Server table. Since the speed of inserting data in SQL Server dependents on how many writes occur to the transaction log, the BULK INSERT command can be used to take advantage of minimal logging

Below the BULK INSERT 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' ] 
    )] 

There are several options when using BULK INSERT. I call your attention to these:

ROWTERMINATOR ='row_terminator'

          Specifies the row terminator. The default row terminator is \r\n (newline character). Since my example is base on a csv file, I do not need to override this value.

To start with and for my example, I will take a simple table and a simple csv file.

Here is the script to create table:

CREATE TABLE [dbo].[Users]
(
	[UserID] int NULL,
	[FirstName] [varchar](10) NULL,
	[LastName] [varchar](10) NULL,
	[Country] [varchar](20) NULL
) ON [PRIMARY]

 Below an image of my csv file:

Now, all I need to do is run the T-SQL below:

TRUNCATE TABLE Users;
GO
BULK INSERT Users
   FROM 'C:\UsersFile.csv'
   WITH (
	  FIRSTROW = 2,
      FIELDTERMINATOR = ','
   )
   ;
SELECT * FROM Users

FIRSTROW = 2 - because my file has an header row.

FIELDTERMINATOR = "," - because the default value is \t (tab character)

And there is a total of 4 rows inserted: