当前位置: 动力学知识库 > 问答 > 编程问答 >

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

问题描述:

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.

网友答案:

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.
网友答案:

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

分享给朋友:
您可能感兴趣的文章:
随机阅读: