Solved

200 Points  For NEWID

Posted on 2002-07-08
13
733 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 information from SQL Server on Database, Connection and Server properties
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.

735 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