SQL Server 2008 - Bulk Insert

[uksql]

All,

Version - SQL Server 2008 R2 ,SP1
Trying to do a bulk insert but getting the below error

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 8

Below is the command for bulk insert
bulk insert aaa_user_session
from 'T:\AAA_USER_SESSION.txt'
with
( FIRSTROW = 3,
ROWTERMINATOR = '\n',
FIELDTERMINATOR = ','
)

CREATE TABLE [dbo].[AAA_USER_SESSION](
[USR_SESS_TOK] [varchar](64) NULL,
[CLNT_REF_ID] [varchar](64) NULL,
[USR_SESS_EXPR_TIME] [datetime] NULL,
[ID] [varchar](50) NULL,
[USM_CREATOR] [varchar](50) NULL,
[USM_CREATE_TS] [datetime] NULL,
[USM_UPDATOR] [varchar](10) NULL,
[USM_UPDATE_TS] [datetime] NULL
)

Sample first 5 lines of the file

USR_SESS_TOK,CLNT_REF_ID,USR_SESS_EXPR_TIME,ID,USM_CREATOR,USM_CREATE_TS,USM_UPDATOR,USM_UPDATE_TS
------------,-----------,------------------,--,-----------,-------------,-----------,-------------
12349876,QA0909,2009-10-09 13:16:09.577,NULL,suresh,2009-10-09 12:34:59.320,NULL,2009-10-09 12:46:17.033
12349876,QA0909,2009-10-09 15:16:46.063,NULL,suresh,2009-10-09 13:46:58.353,NULL,NULL
12349876,QA0909,2009-10-09 15:18:44.110,NULL,suresh,2009-10-09 13:48:56.433,NULL,NULL

Is the last column usm_update_ts being null the issue? How do I insert the data?

I even tried bcp command but it gave me the below error but it inserted first row but gave an error on the 2nd and 3rd.

Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification

exec master..xp_cmdshell 'bcp AP_PP..AAA_USER_SESSION in "T:\AAA_USER_SESSION_TEST.txt" -S SCLPP01 -c -t, -T'


Thank you all in advance for your help.

 

[gmmastros]

Is the last column usm_update_ts being null the issue? How do I insert the data?

Yeah. Kinda.

The problem is that the value is not null, it's the character string "NULL", which is really a four letter word. Since your 8th column is defined as a DateTime, and "NULL" cannot be converted to DateTime, you are getting the error. If you replaced all your "NULL" values with empty strings, then your import would work.

[tt]
USR_SESS_TOK,CLNT_REF_ID,USR_SESS_EXPR_TIME,ID,USM_CREATOR,USM_CREATE_TS,USM_UPDATOR,USM_UPDATE_TS
------------,-----------,------------------,--,-----------,-------------,-----------,-------------
12349876,QA0909,2009-10-09 13:16:09.577,,suresh,2009-10-09 12:34:59.320,,2009-10-09 12:46:17.033
12349876,QA0909,2009-10-09 15:16:46.063,,suresh,2009-10-09 13:46:58.353,,
12349876,QA0909,2009-10-09 15:18:44.110,,suresh,2009-10-09 13:48:56.433,,
[/tt]

You could do this:

1. Open the file
2. Replace NULL with empty string
3. Save the file
4. import the data

The problem with this approach is that it's likely to be slow. Instead, I would suggest:

1. Create a temp table where all of the columns are varchar.
2. import to the temp table
3. move the data to the real table.

Like this:

-- Create a temp table with all varchar's
Create TABLE #AAA_USER_SESSION(
[USR_SESS_TOK] [varchar](64) NULL,
[CLNT_REF_ID] [varchar](64) NULL,
[USR_SESS_EXPR_TIME] [varchar](40) NULL,
[ID] [varchar](50) NULL,
[USM_CREATOR] [varchar](50) NULL,
[USM_CREATE_TS] [varchar](40) NULL,
[USM_UPDATOR] [varchar](10) NULL,
[USM_UPDATE_TS] [varchar](40) NULL
) 

-- insert from file to temp table
bulk insert #aaa_user_session
from 'C:\SampleFile.txt'
with
( FIRSTROW = 3,
ROWTERMINATOR = '\n',
FIELDTERMINATOR = ','
)

-- move data to real table while handling NULL dates.
Insert Into AAA_USER_SESSION(USR_SESS_TOK, CLNT_REF_ID, USR_SESS_EXPR_TIME, ID, USM_CREATOR, USM_CREATE_TS, USM_UPDATOR, USM_UPDATE_TS)
Select USR_SESS_TOK, 
       CLNT_REF_ID, 
	   Case When IsDate(USR_SESS_EXPR_TIME) = 1 Then USR_SESS_EXPR_TIME END, 
	   ID, 
	   USM_CREATOR, 
	   Case When IsDate(USM_CREATE_TS) = 1 Then USM_CREATE_TS END, 
	   USM_UPDATOR, 
	   Case When IsDate(USM_UPDATE_TS) = 1 Then USM_UPDATE_TS END
from   #aaa_user_session

-- Drop the temp table
Drop Table #aaa_user_session

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom