SqL Statement

Hi,
I have this store procedure.  where I first created a Merge temp table.  filled the table with data then I get the MaxNum the count.  I am having problem with do while loop I want to say while the MaxNum <= totaltexaco update the temp table
so if the Maxnum is 50 I want to update the temp table and set the internalid with numbers start with 51 up to the count number for all the records in the tables.

Create Procedure dbo.ChevronTexacoMerge As

Declare @MaxNum as int
Declare @CountTexaco as int

Set NoCount On

Create Table #MergeTemp
(
MergeId int IDENTITY PRIMARY KEY,
ExternalID varchar(20),
InternalID int,
ChCustomerID varchar(20),
IVRPin int,
SystemDate smalldatetime
)

--Fill the Mergetemp table with texaco data only
--This will get all ExternalID from Texaco that do not
--have any match with chevron.
Insert Into #TempTable
(
InternalID,
ChCustomerID,
IVRPin,
SystemDate,
ExternalID
)
SELECT dbo_ExternalPointerReference1.InternalID, dbo_ExternalPointerReference1.ChCustomerID, dbo_ExternalPointerReference1.IVRPin, dbo_ExternalPointerReference1.SystemDate, dbo_ExternalPointerReference1.ExternalID
FROM dbo_ExternalPointerReference1
WHERE (((dbo_ExternalPointerReference1.ExternalID) Not In (select dbo_ExternalPointerReference.ExternalID from dbo_ExternalPointerReference)));

--GET the highest InternalID number from the chevron table.
Set @MaxNum = (SELECT Max([InternalID]) AS [Max]
FROM dbo_ExternalPointerReference;) + 1

--Get the count of texaco data in the temptable.
Set @CountTexaco = SELECT Count([ExternalID]) AS ExternalIDCount
FROM #MergeTemp;

   Set @TotalTexaco = @MaxNum + @CountTexaco

--say @MaxNum is 50
--say @CountTexaco is 10
--say @TotalTexaco is 60

Begin            
--here I want to fill InternalId with a number larger than @MaxNum up to the CounTexaco so 51-60 starting from the first record to the end.            
      Do While (@MaxNum <= @TotalTexaco)
            update #MergeTemp set InternalID= @MaxNum = @MaxNum + 1
            where MergeId = @i = @i + 1
   Loop
            
End
Start Free Trial