SQL DB help please

[Screeper]

Morning all,

I am currently temping for a company, doing boring stuff like cleaning data for the db etc.. (yes I do nod off occassionally)
My contract ends this friday.
However, today my boss came and asked me if it was possible to help them sort out a 'small' db problem that they have.

Problem:
System: MS Server 2003 running MS SQL Server 2000.
They were (up until end of 2006), using Tomcat (which I know nothing about) to insert the data into the db.
However, they upgraded the server from MS 2000 to MS 2003 at the end of 2006 and now Tomcat won't work as it is "not compatible" (direct quote from the single IT employee) with Server 2003.
So they have hundreds of gigs worth of data sitting in .csv files waiting to be imported into SQL but they haven't been able to do it.

Now I'm not even sure if Tomcat really isn't compatible or they have just decided it isn't. Is there not a newer version that can be used?

Anyway my question to you guru's is what program/solution woud you use to import loads of data into the db on the system above.
I did study SQL 2000 at college but that was a few years ago so my skills are not what you'd describe as 'polished'

I've probably got until Friday to come up with an idea for the boss. If he agrees then my contract will be extended which would be great

Any ideas would be hugely appreciated.

[SilverBack]

Do they need some sort of interface to upload CSV files to the server, and then have them import the CSV data into SQL 2000?

[viceroy]

BCP R UR Friend

link1
link2
http://msdn.microsoft.com/en-us/library ... L.80).aspx <- stupid brackets are breaking link so just copy and paste the whole lot

Have fun

[Screeper]

Yes, an interface would be ideal.
But if that isn't feasible then any efficient method of uploading will do.

[viceroy]

BCP R UR Friend

link1
link2
http://msdn.microsoft.com/en-us/library ... L.80).aspx <- stupid brackets are breaking link so just copy and paste the whole lot

Have fun

Like I said...BCP is the most efficient

[Screeper]

Yes thanks, Vice I was typing to SB as you posted your first answer.

I'm reading through those tutorials atm. Looks a bit complicated.
Although as I'm reading through it, the cobwebs are clearing a bit, I do seem to remember using bcp in a SQL prac at college.

Will run a few dummy imports this evening to see how it goes.

[viceroy]

Cool.

If you don't really come right with the BCP scripts, maybe look into DTS packages as well.

[SilverBack]

Sorry for the daft question, but why not just import the data via SQL 2000 import method? I recall it having a method to import, where you select the file, the delimiter etc etc. Sorry...been a while since I used 2000, on 2005 at the mo...but Im sure I recall something like that?

[viceroy]

Sorry for the daft question, but why not just import the data via SQL 2000 import method? I recall it having a method to import, where you select the file, the delimiter etc etc. Sorry...been a while since I used 2000, on 2005 at the mo...but Im sure I recall something like that?

That will work, but BCP, while a bit more difficult as it's not GUI is much more efficient

[Screeper]

Am currently looking at DTS.
I don't have access to the SQL server until tomorrow so I can't play around on it.

SB - I'm going to have a look at the built-in import feature of sql 2000 as soon as I get access to the server.
But I believe BCP may be the way to go for huge chunks of data.

Thanks for the help guys.

[SilverBack]

Eish...sorry with late reply, been swamped at work. Ok kewl, Im sure either will work, and ja...if big data, will work. Good luck