Solved

200 Points  For NEWID

Posted on 2002-07-08
13
728 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
  • 6
  • 6
13 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
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
 
LVL 69

Expert Comment

by:ScottPletcher
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:ScottPletcher
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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:ScottPletcher
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:ScottPletcher
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now