Using BULK INSERT, questions and tips...

[LucieLastic]

hi

I'm in the process of moving entire databases from Sybase to SQL Server 2000 and have some questions:

Q1. The DB build script I've generated in Sybase for all the DB objects include everything (constraints, indexes etc) - do I need to omit these and generate the tables on their own? Then BULK INSERT the data and add the constraints, indexes after?

Q2. Do I need to lock the table before doing the BULK INSERT?

Q3. Is there anything else I should be taking in to consideration as I'm about to try and move a pretty hefty database?

This is new to me. I've done it already with a small database but for that I had the constraints & indexes in place before doing the BULK INSERT, I also didn't lock any tables. So I'm grateful to hear of any hints, tips or suggestions.

Many thanks in advance
lou

p.s. I'm doing the move in to a development environment, so I have room for error, fortunately.

 

[nigelrivett]

Q1 do I need to omit these and generate the tables on their own? Then BULK INSERT the data and add the constraints, indexes after

That sounds good

Q2 Do I need to lock the table before doing the BULK INSERT?

No - it might be a bit better if you use the table lock option on the bulk insert but I assume nothing will be trying to access tables while you are doing this.

Q3. Is there anything else I should be taking in to consideration

Watch out for dates, collations (case sensitivity?), unicode, ...
I assume you are running test loads on small tables first.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[LucieLastic]

hi Nigel

"I assume you are running test loads on small tables first."
The first DB I did was small but my boss wants me to try one of the bigger databases, as we have several DBs which are pretty hefty and only a couple of little ones. The hefty ones can't have too much down time either so he's interested to know how long the process would take.

Funnily enough, I haven't encountered a problem with date formatting (yet). Sybase BCPed dates as 'dd mmm yyyy' and SQL Server appears to have loaded them correctly. Did have a couple of problems with MEMO fields though - not sure what happened there, need to look in to it - Bcp had put it over more than one line in the file.

lou