Hey guys,

Wondering if you can help me. My project basically need a way to get a load of data from a massive database and then run a query in SQL to get more data. It's a little complicated so i'll explain:

I've got an SQL query to get a list of host keys. There's 38,000 (students at leeds university!) so doing it manually is a bit of a no-no. I then need to run another query on all 38,000 of those and save each output as a .csv file. Might take an age so that's fine.

I was thinking, would it be possible to use another language (say, Python cos its easy) to open up a list of all the host keys, run an SQL query on each of them and save them? Or even better would it be possible in SQL?

Why do you have to query the database twice? Surely you query it once, getting all the relevant records, and then you deal with them how you see fit (in this case it seems to be outputting them to a csv file). And yes, for just outputting to a simple text file I'd use something like Python.

Well the first query would be to get the host keys, the second query would be to use said host keys to bring up that person's timetable. But if there's another way to do it i'm very open to suggestions - i'm an absolute novice at all this! Could there be a way to run one query which would take every host key and put it through the timetable fetching query?

I'd imagine so, depending on the database structure, but you'd have a query a bit like:



I'd have a looksy at w3schools.com for more ideas of what can be done with SQL.

Is it MS SQL server?

If so do it in .Net, then you gain file writing and direct DB interaction and it's a cleaner way to use it than manually running multiple actions by just running one

Good point, although by the sounds of it this won't take 50 lines of Python, and is only going to be used once or twice, so I'd just clobber it together and be done with it!

with a link to the dbml....

foreach(person s in persons)
{
// pull the host key (if you need to)
..

// create the csv
s.CreateCSV(string.format("Someplace/{0}.csv", s.id);
}

then have a method to create the CSV from the object

bish bash bosh, jobs a good'un

Hm, I shall definitely be looking into this .net thinger... How hard is it to do something like this from no experience in about a day?

Using a sub-select as the WHERE clause should work.

I still reckon it's overkill for something this trivial, and locks you into Windows! Sorry Finlay! (I do want to pick up .Net dev again, it's just kind of pointless for uni coursework as everything has to compile on a unix box) (8-p)

Okay after looking into .Net...

I think I know LESS about it now than I did when I started.

Python it is!

[quote="Angelic"]Okay after looking into .Net...quote]
It's definitely worth looking into at some point (assuming you want to do MS development), I just wouldn't bother for this.

I might at some point recreationally, but it probably won't be to do professionally - i'm just not good enough!

Right this is the query that brings up my own timetable:

GET_TIMETABLE

And this is the query that gets all the host keys:

GET_HOST_KEYS

To be safe, just to test it works, I can add:

GET_SPECIFIC_HOST_KEYS

and that will only bring up 200 results (the Computing students).

Is there any way to combine these? It's highly confusing me... Or will this be a case of running the GET_HOST_KEYS query, saving it as a CSV and from within python running a loop to run the GET_TIMETABLE query for each row in the CSV?

Just replace the specific host key with the department where clause... At least for the second 2.

What is the relation between SDVR_ALLC_SET and SDO_STUDENT_SET?

I don't quite understand? Replace what with whonow? Sorry to be so dense!

I'm not sure what the relationship is entirely, but I believe it's something like SDO_STUDENT_SET is where my key is found, and SDVR_ALLC_SET is where the SDO_STUDENT_SET is as they're separated into staff and students...

I could be entirely wrong though. The lovely lady that guided me through it spoke quite a lot of jargon and keeping up with her was a challenge to say the least.