SQL Insert Into and then Update Loop in Stored Procedure

I am trying to Insert Into an existing table (DestinationCountryNew) 195 records from source table (DestinationCountry),
then after each 195 records are inserted I need to update all OriginID fields in that batch of 195 to the next incremental number until OriginID field = 195.   Should end up with 38025 records in total, 195 * 195.  So first batch of 195 the OriginID will be 1, the second batch 2, etc.

Here is my Procedure that execute2 but is caught in a loop and the OriginID's are null.

ALTER Procedure [dbo].[InsertThenUpdateCountryDestinationNew]
AS
DECLARE @OriginCountryID Int
SET @OriginCountryID = 1
WHILE (@OriginCountryID <= 195)
BEGIN

INSERT INTO CountryDestinationNew
( Country, CountryCode, OriginCode)
SELECT  Country, CountryCode, OriginCode FROM CountryDestination

UPDATE CountryDestinationNew
SET @OriginCountryID = @OriginCountryID + 1
WHERE @OriginCountryID = null
      
End
ExpressMan1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

typetoitCommented:
You need to increment the @OriginCountryID in the procedure not just the database.  Also you could add the @OriginCountryID +1 in the insert query.  See below:

ALTER Procedure [dbo].[InsertThenUpdateCountryDestinationNew]
AS
DECLARE @OriginCountryID Int
SET @OriginCountryID = 1
WHILE (@OriginCountryID <= 195)
BEGIN

INSERT INTO CountryDestinationNew
( Country, CountryCode, OriginCode, OriginCountryID)
SELECT  Country, CountryCode, OriginCode, (@OriginCountryID + 1) AS OriginCountryID  FROM CountryDestination

@OriginCountryID = @OriginCountryID + 1
     
End
0
_agx_Commented:
Ignoring the sql for a moment, what's the overall purpose of this process in plain english (not sql)?  Seeing manually incremented "ID" columns usually sets off red flags for  potential problems with duplicate values.
0
ExpressMan1Author Commented:
typetoit

Got an error, ?
Msg 102, Level 15, State 1, Procedure InsertThenUpdateCountryDestinationNew, Line 12
Incorrect syntax near '@OriginCountryID'.

agx

Trying to create a Destination Country table to be used in FedEx rating project in C# .net using 4 cascading dropdown lists.  ddlOriginCountry, ddlDestinationCountry, ddlService, ddlPackageType.

ddlservice (Services available)  is dependent on combination of OriginCountry and DestinationCountry selected. I could be on the wrong track altogether and please tell me if I am.

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

arnoldCommented:
I think you need to use Set on the assignment of the next number.
set @OriginCountryID = @OriginCountryID + 1

One thing the addition of 1 in the select is unnecessary as it will mean the range of originCountry will be from 2-196 instead of 1-195.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ExpressMan1Author Commented:
That did it.  Thanks to all.
0
_agx_Commented:
@ExpressMan1 - Seeing column names like "ID" raised questions about whether the values needed to be unique within the table.  If so, it could be problematic if the proc isn't a one time event, but if it is a one off .. or nothing catastrophic happens if the numbers are duplicated it should be fine.
0
ExpressMan1Author Commented:
agx,

Just a one time making of the table.

Thanks for the comments and help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.