June 18, 2008 at 12:55 pm
I'm realtive new to SQL coding, I have a question on my below code. I'm trying to update differnt column on same table based on the @incr, this variable is associated with a client, which could range from 1 to 10 based on differnt client. I'm currently use cursor to do this one by one. is there anyway i can avoid using cursor in this situation ? Code is something like
declare My_Cur for select GNo, Gvd, Gva from TableA order by date
open My_Cur
fetch next from My_cur into @GNo, @GVD, @Gva
while @@FETCH_STATUS = 0
Begin
If @Incr = 1 Begin update TableA set ADate001 = @GVD, AAmt001 = @Gva where GNo = @G_No End
If @Incr = 2 Begin update TableA set ADate002 = @GVD, AAmt002 = @Gva where GNo = @G_No End
If @Incr = 3 Begin update TableA set ADate003 = @GVD, AAmt003 = @Gva where GNo = @G_No End
If @Incr = 4 Begin update TableA set ADate004 = @GVD, AAmt004 = @Gva where GNo = @G_No End
...
set @VestIncr = @VestIncr + 1
fetch next from my_cursorV into @GrantNumi, @GVD, @Gva
end
June 18, 2008 at 1:09 pm
Yes, there like is. As of right though - you're not specifying where or how you're getting @incr (you use it, but you're not retrieving /set its value.). Are you pulling it out of the cursor, or is that globally set ahead of time? It just sounds like you're not describing enough to give you a complete answer: try to supply some more specifics on how that would be derived.
Depending on how that's set, there would be several options on how to approach that.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 18, 2008 at 1:09 pm
This screams poor table design... why don't you have one row per date?
If this is the case of a report you need to run, or something of the likes, or that you can't change the table design then answer this question :
how do you set this value : @Incr?
Is it simply random or is there something in the base table that helps us identify this value?
June 18, 2008 at 1:18 pm
Guess why, it is a vendor software, I can't change table design.
The vendor send a large ASCII file for multiple clients which it loaded to a staging table.
The @Incr initilzed at 1 in the beginning of cursor, if you noticed the original SQL has an order by which identify the value. But as I mentioend, I can't tell how many increment each client has until read from input table.
June 18, 2008 at 1:25 pm
I really appreciate someone come up help so fast. OK before there is another piece of code to open another cursor like below (its like cursor in a cursor which I know is performance killer, staging tbale has almost one million and need popluate data to mutilple million rows tableA)
declare MG cursor for
select Grant_number_G from staging_tbl
open my_cursorG
set @VestIncr = 1
fetch next from MG into @GNo
while @@FETCH_STATUS = 0
BEGIN
......
June 18, 2008 at 1:41 pm
John - The little abbreviated snippets just don't make sense to me. i just can't quite figure out how they're going together.
Let's start basic - try to start by laying out some basic info to show what's there and where things need to go. Pulling one row out of the staging table at a time just plain doesn't make sense if you can do it set-based, but with the info we have now - I couldn't tell you what to implement. seeing what the incoming data, the staging table, and the final tables look like would be important too.
Anyway - start here:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]
Try to give us as much info on the problem as you can. Help us help you.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 18, 2008 at 2:05 pm
Let me try it I can do better this time, below is the code and some comments
-- read client data from staging table and basd on the increment value, update TableA corresponding columns accordingly
declare MG cursor for
select C_Number from staging_tbl
open MG cursor
set @VestIncr = 1
fetch next from MG into @GNum
while @@FETCH_STATUS = 0
BEGIN
declare My_Cur for select GNo, Gvd, Gva from TableA order by Gdate -- Gdate is a date column,
open My_Cur
fetch next from My_cur into @GNo, @GVD, @Gva
while @@FETCH_STATUS = 0
Begin
If @Incr = 1 Begin update TableA set ADate001 = @GVD, AAmt001 = @Gva where GNo = @G_No End
If @Incr = 2 Begin update TableA set ADate002 = @GVD, AAmt002 = @Gva where GNo = @G_No End
If @Incr = 3 Begin update TableA set ADate003 = @GVD, AAmt003 = @Gva where GNo = @G_No End
If @Incr = 4 Begin update TableA set ADate004 = @GVD, AAmt004 = @Gva where GNo = @G_No End
... -- somehow it can have up to 10 increment
end
set @VestIncr = @VestIncr + 1
fetch next from my_cursorV into @GrantNumi, @GVD, @Gva
end
....
June 18, 2008 at 2:36 pm
John Chiu (6/18/2008)
Let me try it I can do better this time, below is the code and some comments-- read client data from staging table and basd on the increment value, update TableA corresponding columns accordingly
declare MG cursor for
select C_Number from staging_tbl
open MG cursor
set @VestIncr = 1
fetch next from MG into @GNum
while @@FETCH_STATUS = 0
BEGIN
declare My_Cur for select GNo, Gvd, Gva from TableA order by Gdate -- Gdate is a date column,
open My_Cur
fetch next from My_cur into @GNo, @GVD, @Gva
while @@FETCH_STATUS = 0
Begin
If @Incr = 1 Begin update TableA set ADate001 = @GVD, AAmt001 = @Gva where GNo = @G_No End
If @Incr = 2 Begin update TableA set ADate002 = @GVD, AAmt002 = @Gva where GNo = @G_No End
If @Incr = 3 Begin update TableA set ADate003 = @GVD, AAmt003 = @Gva where GNo = @G_No End
If @Incr = 4 Begin update TableA set ADate004 = @GVD, AAmt004 = @Gva where GNo = @G_No End
... -- somehow it can have up to 10 increment
end
set @VestIncr = @VestIncr + 1
fetch next from my_cursorV into @GrantNumi, @GVD, @Gva
end
....
John - this still does not help us. Where are you setting @Incr? I don't see that at all anywhere in your script - but I do see that you set @VestIncr and increment that variable. It is not, however, used anywhere that I can see.
Please, please, please follow the instructions previously referenced. Those instructions show you how to setup/build the create table statements, insert statements to populate the tables and outlines how to identify the expected results. Without that information it is going to be very hard for anybody to provide an answer to this question.
Thanks,
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 19, 2008 at 8:27 am
Below is actual code (except decalre variables). Hope you can shed some light to see how I may can make it works better
declare MG cursor for
select C_Number from staging_tbl
open MG cursor
set @VestIncr = 1
fetch next from MG into @GNum
while @@FETCH_STATUS = 0
BEGIN
declare My_Cur for select GNo, Gvd, Gva from TableA order by Gdate -- Gdate is a date column,
open My_Cur
fetch next from My_cur into @GNo, @GVD, @Gva
while @@FETCH_STATUS = 0
Begin
If @VestIncr = 1 Begin update TableA set ADate001 = @GVD, AAmt001 = @Gva where GNo = @G_No End
If @VestIncr = 2 Begin update TableA set ADate002 = @GVD, AAmt002 = @Gva where GNo = @G_No End
If @VestIncr = 3 Begin update TableA set ADate003 = @GVD, AAmt003 = @Gva where GNo = @G_No End
If @VestIncr = 4 Begin update TableA set ADate004 = @GVD, AAmt004 = @Gva where GNo = @G_No End
... -- somehow it can have up to 10 increment
set @VestIncr = @VestIncr + 1
fetch next from My_cur into @GNo, @GVD, @Gva
end
set @VestIncr = 1
close My_Cur
deallocate My_Cur
fetch next from MG into @G_Num
end
close MG
deallocate MG
June 19, 2008 at 8:57 am
John - start here: http://qa.sqlservercentral.com/articles/Best+Practices/61537/
I have no idea how to help you out with this, you have posted a new script with changes to it and it just opens up more questions. For example, why are you even populating the variable @Gnum? It is not used, as far as I can tell, and that cursor only appears to be used to increment a variable.
Please read the above link and put together the requested information. I am absolutely positive that if you provide the above information someone here will be able to provide a solution.
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 19, 2008 at 8:58 am
Somebody needs to find whoever designed this process on the vendor side, and beat them with a data normalization book. They should go back to designing spreadsheets because they clearly don't have a clue about how to lay out data. It also seems that they want you to rely on the physical order in the staging_tbl, which is another clue that they don't know much about RDBMS'es.
I honestly don't think you'll get much perf gains out of this, but you never know. The design is so bad that there really doesn't seem to be much in the way of improvements left.
You can try something like this:
select identity(int,1,1) as rid,
c_number
into #myTable
from staging_tbl
update TableA
set ADate001 = Gvd,
AAmt001 = Gva
from tableA
inner join #myTable on GNo=c_number
where #myTable.rid=1
update TableA
set ADate002 = Gvd,
AAmt002 = Gva
from tableA
inner join #myTable on GNo=c_number
where #myTable.rid=2
update TableA
set ADate003 = Gvd,
AAmt003 = Gva
from tableA
inner join #myTable on GNo=c_number
where #myTable.rid=3
--keep going until 10
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 19, 2008 at 2:08 pm
Suppressing... urge... to... load... porkchops... on... OP...
PLEASE! You haven't read the article that everyone has been asking you to... let's try this one final time... read, understand, and post using the techniques found in the following article, please.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
--Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply