SQL Server DATETIME TO Oracle DATE
**Update:There is a better, more consistent solution in SQL Developer 1.5.1**
Offline Datamove involves using SQL Server/Sybase BCP tool to dump out the data in the tables into dat files. Then Oracles SQL*Loader is used to load the data into Oracle.
This is the recommended approach when dealing with large datasets, as BCP and SQL*Loader are designed for datamove.
Also SQL Developer will create the scripts for you, so you don't have to manually create them.
One issue that has cropped up is moving DATETIME values to DATE.
SQL Server/ Sybase DATETIME has millisecond precision. Oracle has the TIMESTAMP datatype to hold millisecond values. But sometimes you just want to convert from DATETIME to DATE and ignore the milliseconds as they are not important.
To do this you can specify in the date format mask to ignore any values after the second value. Use dots '.' to do this job.
Date Mask :yyyy-mm-dd HH24:mi:ss......
The SQL*Loader scripts will include this format mask around each of the DATETIME column values being loaded.
If you dont specify in the format mask to ignore the millisecond values , SQL*Loader will not recognize the DATETIME values in the dat files.
Note: The old OMWB used to create a view in the source database todo this, but this solution is allot neater.
Offline Datamove involves using SQL Server/Sybase BCP tool to dump out the data in the tables into dat files. Then Oracles SQL*Loader is used to load the data into Oracle.
This is the recommended approach when dealing with large datasets, as BCP and SQL*Loader are designed for datamove.
Also SQL Developer will create the scripts for you, so you don't have to manually create them.
One issue that has cropped up is moving DATETIME values to DATE.
SQL Server/ Sybase DATETIME has millisecond precision. Oracle has the TIMESTAMP datatype to hold millisecond values. But sometimes you just want to convert from DATETIME to DATE and ignore the milliseconds as they are not important.
To do this you can specify in the date format mask to ignore any values after the second value. Use dots '.' to do this job.
Date Mask :yyyy-mm-dd HH24:mi:ss......
The SQL*Loader scripts will include this format mask around each of the DATETIME column values being loaded.
If you dont specify in the format mask to ignore the millisecond values , SQL*Loader will not recognize the DATETIME values in the dat files.
Note: The old OMWB used to create a view in the source database todo this, but this solution is allot neater.