PDF
asked on
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-S
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(
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'=@
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!!!!!
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-S
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(
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'=@
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!!!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
(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.
Please refresh the comments and look at my comment from 08:54AM.
ASKER
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'+CONVE
But still have same error
Ps
The new numbers don't care for oldid(CAST(SUBSTRING(@id,4
(. When sequence =9999 group(set)= group(set)+1. (002…..etc)
Thanks!
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'+CONVE
But still have same error
Ps
The new numbers don't care for oldid(CAST(SUBSTRING(@id,4
(. When sequence =9999 group(set)= group(set)+1. (002…..etc)
Thanks!
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.
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.
You should fix this :
IF @CurrentSequence > @MaxSequence
begin
select @CurrentSet = @CurrentSet + 1,
@CurrentSequence = @StartSequence
end
IF @CurrentSequence > @MaxSequence
begin
select @CurrentSet = @CurrentSet + 1,
@CurrentSequence = @StartSequence
end
ASKER
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.
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.
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.
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.
ASKER
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'+CONVE
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'=@
go
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'+CONVE
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'=@
go
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.
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.
ASKER
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'+CONVE
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'=@
go
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'+CONVE
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'=@
go
ASKER
Thanks Scott!
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