Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gtr
Starting Member
2 Posts |
 Posted - 2004-12-23 : 10:50:06
|
| I'm Working on a t-sql project that will process a table prepared by a front end.The front end:1) processes a spreadsheet whose contents vary each run (# of cols, col headers, and # of rows).2) creates a sql server table with N cols named COL1, COL2...COLn - all varchar.3) the col headers are mapped to ids and will be passed to my procedure as a comma delimited list (number of entries in this list = N)4) places the spreadsheet data into the tableMy client wants all the processing on the back end (t-sql).The table's cols (quantity, names) are not known to t-sql until runtime. I only know that the cols will be named COL1, COL2, ...COLn and I will receive the comma delimited id list where list entry 1 applies to COL1, list entry n applies to COLn.I need to process each col (look up id info and decide how to continue) in each row. In vb.net I could easily use the rows and cols collections of a table object - but with t-sql I am not sure how to proceed.I'll need a cursor to loop thru the rows - BUT how do i retrieve N columns from that cursor and how can I then loop through each col and do some processing?Any help would be greatly appreciated.Thanks a million (what the heck - Thanks a BILLION)-- gtr |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-23 : 11:04:31
|
| Good grief.So the front end creates a table then passes the data in a csv to an SP.Obvious oddity here - if the front end creates the table why not populate it there too? If you want to encapsulate the processing in an SP then the sp should cretae the table too.You might have problems with the size of the data being being than 8000 chars.You can get the number of columns in the table from syscolumns.You can use a function on the csv string to get each col.seehttp://www.nigelrivett.net/f_GetEntryDelimiitted.htmlJust call it with the string and col number and it will return the value. (You could extract each value in turn using charindex - the code to do this is in that function).Now you just need to create a dynamic sql statement to do an insert for each column.I have something a bit similar which takes the entries from a single column table and uses a metadata table to define the destination tables - but that's a lot more fexible than you need.http://www.nigelrivett.net/MoveImportData.htmlBut if this is really for a lot of different data imports you might consider it. It was developed becausee a company had a few hundred different format files to import and were getting more all the time so this allowed users to add the format to the metadata tables and do the import without involving development.==========================================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. |
 |
|
|
gtr
Starting Member
2 Posts |
Posted - 2004-12-23 : 11:18:57
|
| Thanks so much for your reply.Actually, the front end creates AND populates the table. In addition it sends a comma delimited list of ids for me to use while processing the table.I don't really have a problem traversing the delimited list. Also I am ok getting the value of N (I just count the entries in the list).My problem is how to process the N columns.?How can I define a cursor with a select stmt likeselect col1, col2, col3...colN from table?How can I fetch N cols from a cursorfetch from cursor into @col1, @col2, @col3...@colN?How can I set @col_val = @colX (where x >=1 and <=N)See my problem |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-23 : 11:22:40
|
| I think the problem is maybe that you are trying to use a cursor.If you want all the data from a row into variables then use sp_executesql to get each value in turn. You will have to define a number of variables then fill some of them to the limit of the columns.But why do you want the values in variables. And why row by row processing.==========================================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. |
|
|
|
|
|
|
|
|
dynamically go thru rows AND cols