Solved

200 Points  For NEWID

Posted on 2002-07-08
13
734 Views
Last Modified: 2008-02-01
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!!!!!
0
Comment
Question by:PDF
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 200 total points
ID: 7138119
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7138155
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
 

Author Comment

by:PDF
ID: 7138169
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7138177
Please refresh the comments and look at my comment from 08:54AM.
0
 

Author Comment

by:PDF
ID: 7138390
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7138784
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
 
LVL 9

Expert Comment

by:samopal
ID: 7138883
You should fix this :

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

Author Comment

by:PDF
ID: 7139204
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7141513
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
 

Author Comment

by:PDF
ID: 7141616
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7141639
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
 

Author Comment

by:PDF
ID: 7146830
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
 

Author Comment

by:PDF
ID: 7162064
Thanks Scott!
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question