Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have huge tables in oracle database, appx 1 crore+ (10 million+) rows and want to migrate / copy those table and data in sql server.

Currently I am using Import functionality of SQL Server for this process. But it takes a day for this process and takes too much time.

Is there any better way? Any good outcome or step (SSIS, Any other functional step) to follow for this process?

share|improve this question

1 Answer 1

Separate the two tasks.

One of the good ETL rules to follow is to get in and out of the source system as fast as possible. Dumping the Oracle data to flat files should be orders of magnitude faster than inserting direct, as should the subsequent import of those files to SQL Server.

I've found BULK INSERT and bcp to be less troublesome (i.e. I create and encounter fewer errors) if you forget CSV, avoid format files, and utilise a non-standard delimiter instead. I've got "~¬" in use on a bcp based ETL job at the moment and so far so good, the data dog I'm fetching from has yet to contain that string.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.