Sql Script to loop through results insert copies of each record and update title name ending in -A for example to -B, -C etc.

Need assistance with a SQL Script.  Below are three sql statements. Would I need three scripts or one to loop through and complete the task in bold.  How would I do this?  I understand looping but do not know the variables that are needed and how to set up loops.

select * from DMRepository where tiRecordStatus=1 and chUpdateBy='aukathyw' and vchDocName like '%NEW%- A'-- insert each result of multiple records 10 times to same table  and update -A to -B, -C .... to-K --for example this query returns 50 results each result would be inserted 10 times so 500 records would be inserted in total

select * from DMRepository where tiRecordStatus=1 and chUpdateBy='aukathyw' and vchDocName like '%Used%- M'-- insert each result of multiple records  4 times to same table and update-M to -N, -O ...to -Q

select * from DMRepository where tiRecordStatus=1 and chUpdateBy='aukathyw' and vchDocName not  like '%NEW%' and vchDocName not  like '%Used%'--insert each result of multiple records  15 times to same table and update-A to -B, -C .... to -Q


Below are the insert statements I have been working on,
this   insert each result of multiple records 10 times to same table  and update -A to -B, -C .... to-K

insert into DMRepository (iSiteId,vchDocName,iDocTypeId,vchSubject,vchReply, vchMimeType,chUser,txtLitContent,iContentSize, iEmailType,iFaxType,iPrintType,tiRecordStatus,chUpdateBy,dtUpdateDate,iSurveyId,iAutoType,iTrackingEmail,DMRepositoryDocumentSourceId,OemLocked)
select iSiteId,vchDocName,iDocTypeId,vchMimeType,chUser,txtLitContent,iContentSize, iEmailType,iFaxType,iPrintType,tiRecordStatus,chUpdateBy,dtUpdateDate,iSurveyId,iAutoType,iTrackingEmail,DMRepositoryDocumentSourceId,OemLocked
from DMRepository where tiRecordStatus=1 and chUpdateBy='aukathyw' and vchDocName  like '%NEW%- A'

this  insert each result of multiple records  4 times to same table and update-M to -N, -O ...to -Q
insert into DMRepository (iSiteId,vchDocName,iDocTypeId,vchSubject,vchReply, vchMimeType,chUser,txtLitContent,iContentSize, iEmailType,iFaxType,iPrintType,tiRecordStatus,chUpdateBy,dtUpdateDate,iSurveyId,iAutoType,iTrackingEmail,DMRepositoryDocumentSourceId,OemLocked)
select iSiteId,vchDocName,iDocTypeId,vchMimeType,chUser,txtLitContent,iContentSize, iEmailType,iFaxType,iPrintType,tiRecordStatus,chUpdateBy,dtUpdateDate,iSurveyId,iAutoType,iTrackingEmail,DMRepositoryDocumentSourceId,OemLocked
from DMRepository where tiRecordStatus=1 and chUpdateBy='aukathyw' and vchDocName  like '%Used%- M'like '%Used%- M'


this insert each result of multiple records  15 times to same table and update-A to -B, -C .... to -Q
insert into DMRepository (iSiteId,vchDocName,iDocTypeId,vchSubject,vchReply, vchMimeType,chUser,txtLitContent,iContentSize, iEmailType,iFaxType,iPrintType,tiRecordStatus,chUpdateBy,dtUpdateDate,iSurveyId,iAutoType,iTrackingEmail,DMRepositoryDocumentSourceId,OemLocked)
select iSiteId,vchDocName,iDocTypeId,vchMimeType,chUser,txtLitContent,iContentSize, iEmailType,iFaxType,iPrintType,tiRecordStatus,chUpdateBy,dtUpdateDate,iSurveyId,iAutoType,iTrackingEmail,DMRepositoryDocumentSourceId,OemLocked
from DMRepository where tiRecordStatus=1 and chUpdateBy='aukathyw' and vchDocName  not  like '%NEW%' and vchDocName not  like '%Used%'
jverasqlAsked:
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.

jverasqlAuthor Commented:
This is what I have come up with. I am getting errors with the lines in bold



Declare @CountId as int
Select @CountId = count(1) from DMRepository where tiRecordStatus=1 and chUpdateBy='aukathyw' --and (vchDocName like '%NEW%- A' or vchDocName like '%NEW%- M')

Select * into #Docs from DMRepository where tiRecordStatus=1 and chUpdateBy='aukathyw' --and (vchDocName like '%NEW%- A' or vchDocName like '%NEW%- M')

Declare @CurrentDocName as varchar(255)

Declare @NewCopies as int
Declare @UsedCopies as int
Declare @OtherCopies as int

WHILE @Countid >0 BEGIN
Select  @CurrentDocName top 1 vchdocname from @Docs

IF right(@CurrentDocName,3) = ‘- A’
Begin
Set @NewCopies = 10
While @NewCopies > 0 Begin
   Case
     when @NewCopies=10 then REPLACE(@CurrentDocName, '- A', '- B')
     when @NewCopies=9 then REPLACE(@CurrentDocName, '- A', '- C')
     when @NewCopies=8 then REPLACE(@CurrentDocName, '- A', '- D')
     when @NewCopies=7 then REPLACE(@CurrentDocName, '- A', '- E')
     when @NewCopies=6 then REPLACE(@CurrentDocName, '- A', '- F')
     when @NewCopies=5 then REPLACE(@CurrentDocName, '- A', '- G')
     when @NewCopies=4 then REPLACE(@CurrentDocName, '- A', '- H')
     when @NewCopies=3 then REPLACE(@CurrentDocName, '- A', '- I')
     when @NewCopies=2 then REPLACE(@CurrentDocName, '- A', '- J')
     when @NewCopies=1 then REPLACE(@CurrentDocName, '- A', '- K')
   End

insert into DMRepository (iSiteId, vchdocname,iDocTypeId,vchSubject,vchReply, vchMimeType,chUser,txtLitContent,iContentSize, iEmailType,iFaxType,iPrintType,tiRecordStatus,chUpdateBy,dtUpdateDate,iSurveyId,iAutoType,iTrackingEmail,DMRepositoryDocumentSourceId,OemLocked)
select top 1 iSiteId, @CurrentDocName,iDocTypeId,vchMimeType,chUser,txtLitContent,iContentSize, iEmailType,iFaxType,iPrintType,tiRecordStatus,chUpdateBy,dtUpdateDate,iSurveyId,iAutoType,iTrackingEmail,DMRepositoryDocumentSourceId,OemLocked
from #Docs

set @NewCopies = @NewCopies-1

delete top 1 from #Docs
End
End
Else if right(@CurrentDocName,3) = ‘- M’
Begin
       Set @UsedCopies = 4
While @UsedCopies > 0 Begin
  Case
     when @UsedCopies=10 then REPLACE(@CurrentDocName, '- M', '- N')
     when @UsedCopies=9 then REPLACE(@CurrentDocName, '- M', '- O')
     when @UsedCopies=8 then REPLACE(@CurrentDocName, '- M', '- P')
     when @UsedCopies=7 then REPLACE(@CurrentDocName, '- M', '- Q')
  End


insert into DMRepository (iSiteId, vchdocname,iDocTypeId,vchSubject,vchReply, vchMimeType,chUser,txtLitContent,iContentSize, iEmailType,iFaxType,iPrintType,tiRecordStatus,chUpdateBy,dtUpdateDate,iSurveyId,iAutoType,iTrackingEmail,DMRepositoryDocumentSourceId,OemLocked)
select top 1 iSiteId, @CurrentDocName,iDocTypeId,vchMimeType,chUser,txtLitContent,iContentSize, iEmailType,iFaxType,iPrintType,tiRecordStatus,chUpdateBy,dtUpdateDate,iSurveyId,iAutoType,iTrackingEmail,DMRepositoryDocumentSourceId,OemLocked
from #Docs

set @UsedCopies = @UsedCopies-1

delete top 1 from #Docs
End
End
Else
Begin
       Set @OtherCopies = 15
While @OtherCopies > 0 Begin
  Case
     when @OtherCopies=15 then @CurrentDocName + '- B'
     when @OtherCopies=14 then @CurrentDocName + '- C'
     when @OtherCopies=13 then @CurrentDocName + '- D'
     when @OtherCopies=12 then @CurrentDocName + '- E'
     when @OtherCopies=11 then @CurrentDocName + '- F'
     when @OtherCopies=10 then @CurrentDocName + '- G'
     when @OtherCopies=9 then @CurrentDocName + '- H'
     when @OtherCopies=8 then @CurrentDocName + '- I'
     when @OtherCopies=7 then @CurrentDocName + '- J'
     when @OtherCopies=6 then @CurrentDocName + '- K'
     when @OtherCopies=5 then @CurrentDocName + '- M'
     when @OtherCopies=4 then @CurrentDocName + '- N'
     when @OtherCopies=3 then @CurrentDocName + '- O'
     when @OtherCopies=2 then @CurrentDocName + '- P'
     when @OtherCopies=1 then @CurrentDocName + '- Q'
   End

insert into DMRepository (iSiteId, vchdocname,iDocTypeId,vchSubject,vchReply, vchMimeType,chUser,txtLitContent,iContentSize, iEmailType,iFaxType,iPrintType,tiRecordStatus,chUpdateBy,dtUpdateDate,iSurveyId,iAutoType,iTrackingEmail,DMRepositoryDocumentSourceId,OemLocked)
select top 1 iSiteId, @CurrentDocName,iDocTypeId,vchMimeType,chUser,txtLitContent,iContentSize, iEmailType,iFaxType,iPrintType,tiRecordStatus,chUpdateBy,dtUpdateDate,iSurveyId,iAutoType,iTrackingEmail,DMRepositoryDocumentSourceId,OemLocked
from #Docs

set @OtherCopies = @OtherCopies-1

delete top 1 from #Docs
       End
End
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
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
Query Syntax

From novice to tech pro — start learning today.