CODEWITHSUNDEEP
sql-server-2008t-sqlbulkinsert
NealWalters
NealWalters

Reputation: 18167

BulkInsert into table with Identity column (T-SQL)

1) Can I do a BulkInsert from a CSV file, into a table, such that the table has an identity column that is not in the CSV, and gets automatically assigned?

2) Is there any rule that says the table that I'm BulkInsert'ing into, has to have the same columns in the same order as the flat file being read?

This is what I'm trying to do. Too many fields to include everything... 

BULK INSERT ServicerStageACS  
   FROM 'C:\POC\DataFiles\ACSDemo1.csv' 
   WITH (FORMATFILE = 'C:\POC\DataFiles\ACSDemo1.Fmt');
GO
SELECT * FROM ServicerStageACS;

Error:

Msg 4864, Level 16, State 1, Line 3 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (rowID).

I'm pretty sure the error is because I have an identity.

FMT starts like this: 

9.0
4
1       SQLCHAR       0       7       ","      1     Month        SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100     ","      2     Client       SQL_Latin1_General_CP1_CI_AS

Upvotes: 1

Views: 3410

Answers (1)

NealWalters
NealWalters

Reputation: 18167

A co-worker recommended that it was easier to do the bulk insert into a view. The view does not contain the identity field, or any other field not to be loaded. 

truncate table ServicerStageACS
go
BULK INSERT VW_BulkInsert_ServicerStageACS  
   FROM 'C:\POC\DataFiles\ACSDemo1.csv' 
   WITH
        (
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n'
        )

GO
SELECT * FROM ServicerStageACS;

Upvotes: 2

Related Questions