How do you increment a temporary table?
-
Select * Into #BC From dfCert -- Creates master Certification table -> #BC Declare @BoardCert Char(3) Set @BoardCert = 'BCn' Declare @Counter int Set @Counter = 1 While @Counter < 10 Begin Select Biog_Nbr, Max(Biog_Cert_ID) as aMaxBiogCertID, Max(Cert_Year) as aMaxCertYear Into #tblBoardCert From #BC Group By Biog_Nbr Delete From #BC From #BC Inner Join #tblBoardCert On #BC.Biog_Nbr = #tblBoardCert.Biog_Nbr and #BC.Biog_Cert_ID = #tblBoardCert.AMaxBiogCertID and #BC.Cert_Year = #tblBoardCert.aMaxCertYear Where #BC.Biog_Nbr = #tblBoardCert.Biog_Nbr and #BC.Biog_Cert_ID = #tblBoardCert.AMaxBiogCertID and #BC.Cert_Year = #tblBoardCert.aMaxCertYear Set @BoardCert = Replace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','') Select * Into #@BoardCert --<< Here's the problem. I need this to be #BC1 then BC2, etc up to BC9 From #tblBoardCert Drop Table #tblBoardCert Set @Counter = @Counter + 1 Print 'The counter has just increased to ' + Cast(@Counter as Char) End
-
Select * Into #BC From dfCert -- Creates master Certification table -> #BC Declare @BoardCert Char(3) Set @BoardCert = 'BCn' Declare @Counter int Set @Counter = 1 While @Counter < 10 Begin Select Biog_Nbr, Max(Biog_Cert_ID) as aMaxBiogCertID, Max(Cert_Year) as aMaxCertYear Into #tblBoardCert From #BC Group By Biog_Nbr Delete From #BC From #BC Inner Join #tblBoardCert On #BC.Biog_Nbr = #tblBoardCert.Biog_Nbr and #BC.Biog_Cert_ID = #tblBoardCert.AMaxBiogCertID and #BC.Cert_Year = #tblBoardCert.aMaxCertYear Where #BC.Biog_Nbr = #tblBoardCert.Biog_Nbr and #BC.Biog_Cert_ID = #tblBoardCert.AMaxBiogCertID and #BC.Cert_Year = #tblBoardCert.aMaxCertYear Set @BoardCert = Replace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','') Select * Into #@BoardCert --<< Here's the problem. I need this to be #BC1 then BC2, etc up to BC9 From #tblBoardCert Drop Table #tblBoardCert Set @Counter = @Counter + 1 Print 'The counter has just increased to ' + Cast(@Counter as Char) End
I guess there is some problem in the counter placement. You are dropping the #TtblBoardCert and then you are incrementing.
Drop Table #tblBoardCert Set @Counter = @Counter + 1So before the first increment itself the table is dropped. Also I have a doubt in this lineReplace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','')Check if it is giving you the correct result or not. Hope this helps. :)Niladri Biswas
-
I guess there is some problem in the counter placement. You are dropping the #TtblBoardCert and then you are incrementing.
Drop Table #tblBoardCert Set @Counter = @Counter + 1So before the first increment itself the table is dropped. Also I have a doubt in this lineReplace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','')Check if it is giving you the correct result or not. Hope this helps. :)Niladri Biswas
I drop the table because it needs to be rebuild on each cycle. The "Max" records are removed on the first cycle and then I pull the next set of "Max" records for the second cycle. This goes on until #BC is empty. Replace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','') actually works. The real problem is the following: Select * Into #@BoardCert From #tblBoardCert It doesn't see #@BoardCert as #BC1 then #BC2 etc., it only sees it as #@BoardCert and so it errors because it thinks it's already written to #@BoardCert on the first cycle.
-
Select * Into #BC From dfCert -- Creates master Certification table -> #BC Declare @BoardCert Char(3) Set @BoardCert = 'BCn' Declare @Counter int Set @Counter = 1 While @Counter < 10 Begin Select Biog_Nbr, Max(Biog_Cert_ID) as aMaxBiogCertID, Max(Cert_Year) as aMaxCertYear Into #tblBoardCert From #BC Group By Biog_Nbr Delete From #BC From #BC Inner Join #tblBoardCert On #BC.Biog_Nbr = #tblBoardCert.Biog_Nbr and #BC.Biog_Cert_ID = #tblBoardCert.AMaxBiogCertID and #BC.Cert_Year = #tblBoardCert.aMaxCertYear Where #BC.Biog_Nbr = #tblBoardCert.Biog_Nbr and #BC.Biog_Cert_ID = #tblBoardCert.AMaxBiogCertID and #BC.Cert_Year = #tblBoardCert.aMaxCertYear Set @BoardCert = Replace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','') Select * Into #@BoardCert --<< Here's the problem. I need this to be #BC1 then BC2, etc up to BC9 From #tblBoardCert Drop Table #tblBoardCert Set @Counter = @Counter + 1 Print 'The counter has just increased to ' + Cast(@Counter as Char) End
This just sounds weird, without going through the details of your script I think you can achieve this in 1 table by including a setID. If you need to create an incremented value over a set you can use row_number (over setID). Creating and dropping temp tables is just bad design.
Never underestimate the power of human stupidity RAH