August 4, 2003 at 4:48 am
I have a complicated csv file structure i have to create from relation data.
Take for example an inital treatment of cancer. Say for example there are 10 types of treatment and recorded are 3 bits of information. In A relational stable this is easy. But the the extraction format (its a DOH structure not mine) consists of 30 columns 3 for each treatment, and i only need to fill in the 1st treatment they had, therefore 27 fields will allways be null.
Compound that with the fact that the CSV has an aditional 25 fields which have to be extracted into this file structure.
What I have done is create a table for extracting based on the spec. I run a query that bring back a list of PK's to run through and insert them into the table.
Now i want to run multiple stored procedures to extract the relevant data based on each PK and insert it into the relevant columns associated with that data.
How would i go about using a cursor for doing this?
I could program this in .Net very easily but I want to achieve this without resorting to client side code if i have to.
Any help is really appricated.
August 4, 2003 at 7:11 am
Don't need a cursor.
If each proc updates the extract table using the PK then write one proc to EXECUTE all the rest.
Another way is to number the procs from 1 (e.g. proc1,proc2,proc3 etc) and use a loop to execute them
DECLARE @counter INT,@sql nvarchar(100)
SET @counter = 0
WHILE (@counter < [max number of procs])
BEGIN
SET @sql = 'EXECUTE procname'+CAST(@counter AS varchar)
EXEC sp_executesql @sql
END
Or just write one proc that updates your extract table by LEFT JOINing all the updating tables.
Far away is close at hand in the images of elsewhere.
Anon.
August 6, 2003 at 2:00 pm
There are a number of ways this can be aproached. I prefer avoiding cursors when ever possible as I find they tend to consume a lot more time to execute. Cursor solutions may seem easier to build, but tend to become more difficult to maintain (IMHO).
One aproach for this is to use a large Update structure, with many joins against the same table, all aliased with specific conditions. Another way which is a variation on this is to do a dynamic update, using the exec command. I would thus name all these 30 fields like C1... C30, then the update would be something like
S = 'update #temp Set C'+ Convert(VarChar, Idx) + ' where some condition'
then call the Exec(S) to execute this.
Hope this helps,
Quentin
August 8, 2003 at 1:10 am
I would take a look at BOL for 'Pivot Table'. This is a classic case where you need to pivot the data on the values. They are a royal pita but for this type of thing it would be the only way to go. The only hope for doing this though is if the values you are pivoting on are fixed so that you can write the complicated case statements to make it work. I would provide you with a sample but my home machine doesn't have SQL installed and my mind is mush right now.
Basically what I would do for this is to have an SP that you pass in the filename and then create some dynamic sql to pivot the data and insert it into the file using OPENDATASOURCE. The problem here is that you would need to use an XLS format rather than a CSV. Otherwise you could fill a table with the data and then use DTS to export it to the CSV file.
As far as needing to run multiple SPs based on the PK values. Could you convert these into UDFs that return tables so that you can do this in a more set based manner?
Good luck on this and let us know how it comes out! 🙂
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply