I am bulk importing a csv file to SQL server 2008, the csv file has been generated from exporting the table data from Oracle SQL developer.
The data for one column in that csv file is in TIMESTAMP(6) for which I am having the DATETIME2(6) datatype for the required column in the SQL server 2008.
I am importing the CSV file using the below statement
BULK INSERT H_CLAIMS.dbo.APPLICATION_QUEUES
FROM 'D:\MyWork\HC DB Work\HCAIDDB_CSV_EXPORTS\APPLICATION_QUEUES_export.CSV'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
while doing above I am getting the below error
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 5 (CREATED_DATE).
Msg 4864, Level 16, State 1, Line 1
The sample data in the column mentioned in the error is like
21-NOV-14 08.57.51.565214000 AM
So I am looking for the answer, which can overcome this issue with any other attributes during the bulk insert statement or any convert function which can properly convert the datetime in the sample data to SQL SERVER 2008 datetime2 format.
SQL Server doesn't know how to convert the text value " 21-NOV-14 08.57.51.565214000 AM" to a DATETIME2 column. Try it in a query analyser window :
SELECT CAST('21-NOV-14 08.57.51.565214000 AM' AS DATETIME2(6))
Note that if you're using DATETIME2(6) it'll be loosing precision compared to what you're trying to import. Have a look at http://msdn.microsoft.com/en-GB/library/bb677335.aspx.
When I've had to do this coming from DB2 text files, I've done it two different ways.
As I couldn't find any bulk Insert which will do the work for me, I have gone with a different approach. After many trails with cast and convert, I followed the below approach which is working as expected I have created a function which can convert the oracle timestamp(6) to nvarchar of sql which can be directly inserted as datetime2(6) datatype in sql server 2008. Below is the function Then I have used a stored procedure which can accept the file path as input parameter and a temp table to hold the nvarchar based datetime2 value. In the stored procedure I have used the dynamic bulk insert statement to insert into the required table. The procedure is after the function
CREATE FUNCTION DATETIMECONVERTER ( @ORACLETIMESTAMP NVARCHAR(100) )RETURNS nvarchar(100) AS BEGIN DECLARE @convertedString nvarchar(100); select @convertedString= replace(@ORACLETIMESTAMP,'.',':'); RETURN STUFF(@convertedString, CHARINDEX(':', @convertedString,18), 1, '.') END GO CREATE PROCEDURE IMPORT_APPLICATION_ROLES @PATH varchar(1000) AS IF OBJECT_ID('H_CLAIMS.DBO.TEMP_APPLICATION_QUEUES', 'U') IS NOT NULL DROP TABLE H_CLAIMS.DBO.TEMP_APPLICATION_ROLES CREATE TABLE H_CLAIMS.DBO.TEMP_APPLICATION_ROLES ( ROLE_ID INT NOT NULL, ROLE_NAME NVARCHAR(255), ROLE_DESC NVARCHAR(255), CREATED_BY NVARCHAR(100), CREATED_DATE NVARCHAR(100), UPDATED_BY NVARCHAR(100), UPDATED_DATE NVARCHAR(100) ) DECLARE @bulkInsert NVARCHAR(4000) = 'BULK INSERT TEMP_APPLICATION_ROLES FROM ''' + @PATH + ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''\n'' )'; EXEC(@bulkInsert) INSERT INTO APPLICATION_ROLES (ROLE_ID,ROLE_NAME,ROLE_DESC,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE) SELECT ROLE_ID,ROLE_NAME,ROLE_DESC,CREATED_BY,dbo.DATETIMECONVERTER(CREATED_DATE)AS CREATED_DATE, UPDATED_BY,dbo.DATETIMECONVERTER(UPDATED_DATE) AS UPDATED_DATE FROM H_CLAIMS.dbo.TEMP_APPLICATION_ROLES DROP TABLE H_CLAIMS.DBO.TEMP_APPLICATION_QUEUES GO
to execute the statment I have used the below statement
EXEC H_CLAIMS.DBO.IMPORT_APPLICATION_QUEUES @PATH='D:\my_export.CSV';
Make sure to place the .csv files in the server machines drive while executing the stored procedure