200 Points For NEWID

I have copy of rdb on SQL Server 2000.
Db has 22 tables and All of the tables
have field ID(PK) & relationships between .

I need to assign NEWID to all records in this format  
00-001-0001(dbcode-Group-Sequence).
MaxSequence is 9999.
ID field was char (6) like '012345'.NewID is char (11)
Hear is my Cursor and Procedure :
CREATE procedure AssignProdNum
    @id varchar (12)  
As
Begin
    Declare @CurrentSet  int,            --1   '001'
            @CurrentSequence   int,      --0   '0000'
            @StartSequence     int,      --1   '0001'
            @MaxSequence       int,      --    '9999'
            @newid             varchar(12) 00-001-0001    

    Select    @CurrentSet = CurrentSet,              
              @CurrentSequence = CurrentSequence + 1,  
              @StartSequence= StartSequence,          
              @MaxSequence = MaxSequence            
         
 From Table2

--assign new id '00-001-0001'

Select  @newid = convert(varchar(4), @CurrentSequence)  
       if len(@newid) = 1  select  @newid = '000' + @newid
       if len(@newid) = 2  select  @newid = '00' + @newid
       if len(@newid) = 3  select  @newid = '0' + @newid
       if len(@newid) = 4  select  @newid = @newid
     
    if @CurrentSequence > @MaxSequence
     select @CurrentSet = @CurrentSet + 1,
     @CurrentSequence = @StartSequence

  -- select @CurrentSet= convert(varchar(3), @CurrentSet)
  --    if len(@newid) = 1  select  @newid = '00' + @newid
  --    if len(@newid) = 2  select  @newid = '0' + @newid
     
     
    select @newid ='00'+'-'+convert(varchar(3),      @CurrentSet)+'-'+ @newid
 
    begin tran

    insert into Table1 (
        ID,
        oldID,
        Field3,
        Field...,
        Field10
       
    ) select
        @newid,
        @id,
        Field3,
        Field...,
        Field10
       
    from Table1
    where ID = @id

    update Table2        set ID = @newid where ID = @id
    update Table...
    update Table22       set ID = @newid where ID = @id

   delete Table1 where ID = @id
   commit tran
   print @newid + ' --> ' + @id
  end --AssignProdNum()
GO
--************************************************************************
--Main
--************************************************************************

declare @id varchar(12),
        @records int,
        @start datetime,
        @finish datetime

declare cur insensitive cursor
for select ID
    from  Table1        
    open cur

select @records = 0,
       @start = getdate()
select 'Start'=@start

fetch cur into @id
while @@fetch_status <> -1
begin
    exec AssignProdNum @id
    select @records = @records + 1
    fetch cur into @id
end

select @finish = getdate()

close cur
deallocate cur

select 'Finish'=@finish,'Count'=@patterns,'Elapsed'=datediff(ms,@start,@finish)
go


I get error:

Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'Table1'.

First record gets NEWID like '00-1-0001'  instead '00-001-0001' in all table.


PLEASE HELP!!!!!
PDFAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You can replace these lines:
Select  @newid = convert(varchar(4), @CurrentSequence)  
      if len(@newid) = 1  select  @newid = '000' + @newid
      if len(@newid) = 2  select  @newid = '00' + @newid
      if len(@newid) = 3  select  @newid = '0' + @newid
      if len(@newid) = 4  select  @newid = @newid

with this line:
SELECT @newid = RIGHT('000' + CONVERT(VARCHAR(4), @CurrentSequence),4)


That change is cosmetic only.  But you need to replace the line:
select @newid ='00'+'-'+convert(varchar(3),      @CurrentSet)+'-'+ @newid

with this line:
SELECT @newid ='00'+'-'+RIGHT('00'+CONVERT(VARCHAR(3),@CurrentSet),3)+'-'+ @newid    
   
0
 
Scott PletcherSenior DBACommented:
Also, you need to use the old @id passed into the AssignProdNum stored procedure to generate the new value.

For example, consider adding these lines to the start of the SP:

CREATE PROCEDURE ...
AS
DECLARE ...
--add these lines before @SELECT newid = RIGHT(...)
SET @StartSequence = 1
SET @MaxSequence = 9999
SET @CurrentSet = CAST(SUBSTRING(@id,4,3) AS INT)
SET @CurrentSequence = CAST(SUBSTRING(@id,7,4) AS INT)
SET @CurrentSequence = @CurrentSequence + 1
--move this line before @SELECT newid = RIGHT(...)
IF @CurrentSequence > @MaxSequence
    select @CurrentSet = @CurrentSet + 1,
    @CurrentSequence = @StartSequence
0
 
PDFAuthor Commented:
OK, now I have right format but still have same Error:
(for all 22 tables)
Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'Table1'.
First record has newid in all tables.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Scott PletcherSenior DBACommented:
Please refresh the comments and look at my comment from 08:54AM.
0
 
PDFAuthor Commented:
Traying to move this line around but is not working.

I did:
After comment
 --assign new id '00-001-0001'

SELECT @newid = RIGHT('000' + CONVERT(VARCHAR(4), @CurrentSequence),4)

SET @StartSequence = 1
SET @MaxSequence = 9999
SET @CurrentSet = CAST(SUBSTRING(@id,4,3) AS INT)
SET @CurrentSequence = CAST(SUBSTRING(@id,7,4) AS INT)
SET @CurrentSequence = @CurrentSequence + 1

IF @CurrentSequence > @MaxSequence
select @CurrentSet = @CurrentSet + 1,
@CurrentSequence = @StartSequence

SELECT @newid ='00'+'-'+RIGHT('00'+CONVERT(VARCHAR(3),@CurrentSet),3)+'-'+ @newid

But still have same error

Ps
The new numbers don't care for oldid(CAST(SUBSTRING(@id,4,3) AS INT)

(. When sequence =9999 group(set)= group(set)+1. (002…..etc)


Thanks!
0
 
Scott PletcherSenior DBACommented:
I was just thinking you could pass in the old number rather than having to write it to a separate table (table2) and then re-read it inside the SP.

If you're still doing all the stuff with table2, I guess that should work too.  Try printing out the value of @newid after the last SELECT that constructs it and see what it prints.  If that number is different, then the logic problem is somewhere else.
0
 
samopalCommented:
You should fix this :

IF @CurrentSequence > @MaxSequence
begin
  select @CurrentSet = @CurrentSet + 1,
  @CurrentSequence = @StartSequence
end
0
 
PDFAuthor Commented:
First record is fine.
Sp print:
000022       --> 00-001-0001
(Update  000022  to  00-001-0001  in all tables)
AND on next one get error:
Server: Msg 2627, Level 14, State 1, Procedure AssignProdNum, Line 40
Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'Table1'.
The statement has been terminated.
Same error for all tables'PK_Table..'
It is not able to write 00-001-0002 to next record or loop to next 9998 records and start over like 00-002-0001.


0
 
Scott PletcherSenior DBACommented:
You should have two lines printed from the SP; if not, move the PRINT to be before the INSERT so that you can verify the value about to be inserted.  It might be 0001 again, which would explain it being a duplicate.

If you are using the logic as shown above, be sure to pass the previous number into the SP.  Or are you still using a write to a different table?  

Please post the current, exact code of the SP.
0
 
PDFAuthor Commented:
Exact code for Sp and cursor.

CREATE procedure AssignProdNum
    @id varchar (12)  
As
Begin
    Declare @CurrentSet  int,           --1   '001'
        @CurrentSequence   int,         --0   '0000'
        @StartSequence     int,         --1   '0001'
        @MaxSequence       int,         --    '9999'
        @newid             varchar(12)  -- '00-001-0001'  
           
/*
    Select    @CurrentSet = CurrentSet,              
              @CurrentSequence = CurrentSequence + 1,  
              @StartSequence= StartSequence,          
              @MaxSequence = MaxSequence            
         
 From Table2
*/
SET @StartSequence = 1
SET @MaxSequence = 9999
SET @CurrentSet =1
SET @CurrentSequence = @CurrentSequence + 1

--assign new id '00-001-0001'

SELECT @newid = RIGHT('000' + CONVERT(VARCHAR(4), @CurrentSequence),4)      
               
        if @CurrentSequence > @MaxSequence
                 select @CurrentSet = @CurrentSet + 1,
                 @CurrentSequence = @StartSequence

     
     
SELECT @newid ='00'+'-'+RIGHT('00'+CONVERT(VARCHAR(3),@CurrentSet),3)+'-'+ @newid  


    begin tran
   
  print @newid + ' --> ' + @id

   
    insert into Table1 (
        ID,
        oldID,
        Field3,
        Field10
       
    ) select
        @newid,
        @id,
        Field3,
        Field10
       
    from Table1
    where ID = @id

    update Table2        set ID = @newid where ID = @id
    update ……
    update Table22       set ID = @newid where ID = @id

   delete Table1 where ID = @id
   commit tran
  -- print @newid + ' --> ' + @id
  end --AssignProdNum()
GO
--************************************************************************
--Main
--************************************************************************

declare @id varchar(12),
        @records int,
        @start datetime,
        @finish datetime

declare cur insensitive cursor
for select ID
    from  Table1        
    open cur

select @records = 0,
       @start = getdate()
select 'Start'=@start

fetch cur into @id
while @@fetch_status <> -1
begin
    exec AssignProdNum @id
    select @records = @records + 1
    fetch cur into @id
end

select @finish = getdate()

close cur
deallocate cur

select 'Finish'=@finish,'Count'=@patterns,'Elapsed'=datediff(ms,@start,@finish)
go

0
 
Scott PletcherSenior DBACommented:
You removed the statements that extracted the current values for @CurrentSet and @CurrentSequence from the input variable @id.  By default, it will start with NULL.  You need to either save the last value to a table or read it from the input parameter.  

You need to understand that variables in a stored procedure are local, not global, which means that each time you call the SP the values reset to their original values.  Thus, AssignProdNum does NOT remember @CurrentSet or @CurrentSequence (or any other variable) from the last time it was executed.  That is why I suggested that you pass the last @id value returned back into the SP so that it knows where to count forward from.
0
 
PDFAuthor Commented:
Ok, I'm back. Sorry ,this is what I was using.
Can you provide same code for changes,Please
and thanks for help.



CREATE procedure AssignProdNum
   @id varchar (12)  
As
Begin
   Declare @CurrentSet  int,           --1   '001'
       @CurrentSequence   int,         --0   '0000'
       @StartSequence     int,         --1   '0001'
       @MaxSequence       int,         --    '9999'
       @newid             varchar(12)  -- '00-001-0001'  
         

   Select    @CurrentSet = CurrentSet,              
             @CurrentSequence = CurrentSequence + 1,  
             @StartSequence= StartSequence,          
             @MaxSequence = MaxSequence            
         
   From Table2


--assign new id '00-001-0001'

SELECT @newid = RIGHT('000' + CONVERT(VARCHAR(4), @CurrentSequence),4)      
             
       if @CurrentSequence > @MaxSequence
                select @CurrentSet = @CurrentSet + 1,
                @CurrentSequence = @StartSequence

     
     
SELECT @newid ='00'+'-'+RIGHT('00'+CONVERT(VARCHAR(3),@CurrentSet),3)+'-'+ @newid  


   begin tran
 
 print @newid + ' --> ' + @id

 
   insert into Table1 (
       ID,
       oldID,
       Field3,
       Field10
       
   ) select
       @newid,
       @id,
       Field3,
       Field10
     
   from Table1
   where ID = @id

   update Table2        set ID = @newid where ID = @id
   update ……
   update Table22       set ID = @newid where ID = @id

  delete Table1 where ID = @id
  commit tran
 -- print @newid + ' --> ' + @id
 end --AssignProdNum()
GO
--************************************************************************
--Main
--************************************************************************

declare @id varchar(12),
       @records int,
       @start datetime,
       @finish datetime

declare cur insensitive cursor
for select ID
   from  Table1        
   open cur

select @records = 0,
      @start = getdate()
select 'Start'=@start

fetch cur into @id
while @@fetch_status <> -1
begin
   exec AssignProdNum @id
   select @records = @records + 1
   fetch cur into @id
end

select @finish = getdate()

close cur
deallocate cur

select 'Finish'=@finish,'Count'=@patterns,'Elapsed'=datediff(ms,@start,@finish)
go
0
 
PDFAuthor Commented:
Thanks Scott!
0
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.

All Courses

From novice to tech pro — start learning today.