BULK INSERT - Identity Column

« 1|2|3 »

This is the 3rd and final post about BULK INSERT. In this one I want to show you how to bulk insert a file into a table with an identity column.

Here is the script to create table:

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

I want to insert a csv file without mapping UserID, since it is an identity column. File format is shown below:

When trying to run script below, I get the error:

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

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (UserID).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (UserID).
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
This problem can be solved in 2 different ways. Either you use a format file as shown in previous post or you can use a view. Let me show you both solutions:

1. View

You cannot insert csv file because you have an extra column in table, wich is identity. But if you create a view without that column, SQL Server allows you to insert data because UserID identity column is unmapped and a number will be automatically generated.

Script to create view:

CREATE VIEW View_Users
AS
SELECT FirstName, LastName, Country
FROM [Users]

Now, all you need to do is running BULK INSERT into View View_Users instead of table Users:

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

2. Format File

Format file allows you to BULK INSERT directly into table Users because a format file is specified. Below the script to insert csv file directly into table Users.

For further detail about how to create a format file, take a look into my previous post.

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