Converting Oracle TIMESTAMP(6) TO SQL SERVER 2008 DATETIME2(6)

Question

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

USE H_CLAIMS
GO
BULK INSERT H_CLAIMS.dbo.APPLICATION_QUEUES
FROM 'D:\MyWork\HC DB Work\HCAIDDB_CSV_EXPORTS\APPLICATION_QUEUES_export.CSV'

WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n') GO

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.

Answer 1

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.

1. Import the datetime field into a varchar then written a bit of SQL to manipulate the string into a format SQL Server can recognise, so something like. Bit slow and clunky, especially if you have a lot of data.
2. Use SSIS and create a transformation to do the string manipulation there. This has the advantage of still being able to bulk insert into the destination table, but does mean you need to be able to have access to integration services.

Answer 2

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';

Answer 3

Make sure to place the .csv files in the server machines drive while executing the stored procedure

Answer 4

I may be late to answer this but allow me to give you my workaround (if the precision doesn't really matter) I import the timestamp from oracle table into SQL 2008 varchar then I update the varchar into a format that will fit for datetime2 then I alter the SQL table column to data type datetime2. EG: in case you have time stamp like '01-JAN-15 12.00.00.000000000 AM +05:30'

update My_Table 
set MyTimeStamp = 
substring(MyTimeStamp, 1,10)+
REPLACE(substring(MyTimeStamp, 11, 8),'.',':')+
substring(MyTimeStamp, 19, 13) 
where MyTimeStamp like '%.%.%.%';

alter table [My_Table] alter column MyTimeStamp DATETIME2; 
Go;