Pitfalls of moving data to SQL Server from Oracle
time to read 6 min | 1181 words

I need to move a couple oftables from an Oracle database to SQL Server, how do I do this?

Well, there is SSMA*, but it has a couple of problems dealing with strange decisions on the Oracle side. It also require a hefty machine to run on, as well as taking quite a bit of time to run.

I used it to generate the schema, and then defined a linked server to Oracle and wrote a very simple script to port the data. It failed, miserably. Let's assume that my script looked like this:

INSERT INTO Customers

(

      [Name],

      [Email],

      [Address],

      [RegisteredAt]

)

SELECT

      [Name],

      [Email],

      [Address],

      [RegisteredAt]

FROM ORACLE..ADMIN.CUSTOMERS

What is there to go wrong? Well, for one thing the range of allowed dates in SQL Server and Oracle is different (1753 - 9999 in SQL, -4712 - 4712 in Oracle). Apperantly some of the customers where registered quite some time ago, about two thousands years ago, to be precise. So I bumped them up to the twentieth century, and tried again.

It failed again. This time it complained about a primary key violation during the copy. That really made me scratch my head. I checked Oracle, and the PK was enabled and doing its job quite well. For the purposed of this conversation, let's assume that the PK is composed of [Name] and [Address].

Can you guess what the problem is now? I couldn't, so I started running the following queries on both SQL Server & Oracle

SELECT

COUNT(DISTINCT [Name]) FROM Customers

SELECT

COUNT(DISTINCT [Address]) FROM Customers

Well, the Address query returned the same count, but the Name query returned a wildly different result. Looking into it a bit, I discovered that I had set of names that looked like this: Alice, ALICE, alice, bob, Bob, BOB, etc.

But SQL Server's case sensitivity is a server wide option. For a couple of minutes, it looked like I was stuck, but then a friend reminded me about the Collation option in the column spesification, and that does allows you to define case sensitivity in the column level. Once I did that, everything worked just fine.

* Sql Server Migration Assistant

Tweet Share Share 0 comments
Tags: