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

jverasql
jverasql used Ask the Experts™
on
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%'
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial