Solved

200 Points  For NEWID

Posted on 2002-07-08
13
726 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Please refresh the comments and look at my comment from 08:54AM.
0
 

Author Comment

by:PDF
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 9

Expert Comment

by:samopal
Comment Utility
You should fix this :

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

Author Comment

by:PDF
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Scott!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

17 Experts available now in Live!

Get 1:1 Help Now