February 16, 2010 at 2:20 am
Hi,
I have a looping script that inserts data into a table and updates the unique column.
I am geting the error message Cannot insert duplicate key row in object' dbo.test' with unique index 'test0'.
Any ideas how i can get round this problem???? please
February 16, 2010 at 2:24 am
There is no 'getting round' the issue, you need to ensure that the data you are updating / inserting does not violate the data integrity.
If you were to provide DDL , sample data and a (simple) reproduction script , we maybe able to help further.
February 16, 2010 at 2:35 am
Thanks for your response.
I have two loops that inserts data into a table, they both work run fine. But if I run one after the other I get the error message. The data rows in both the loops that I am inserting are unique rows so whats cauing the error? Below I have outlined my scripts. All your help will be much appreciated?
Thanks,
declare @col1 int
declare @col2 varchar(50)
declare @rowuno int
declare @now datetime
set @now = Cast(Cast(Year(getdate()) as char(4)) + '-' + Cast(Month(getdate()) as varchar(2))
+ '-' +Cast(Day(getdate()) as varchar(2)) as datetime)
begin
declare empcode CURSOR FOR
select distinct bla1, bla1, bla1 from xxx
open empcode
fetch next from empcode into @col1, @col2, @col3
while @@fetch_status = 0
begin
set @rowuno = (select lastkey from blablabla where tbname = 'xxx')
set @rowuno = @rowuno + 1
insert into aaa
([a],,[c],[d],[e],[f],[g],[h],
[DAYS_OVER_TERMS],[COMM_CR_LIMIT],[COMM_CR_RATE],,[j],[k],[k],
[m],[n],[o],[p],
,[r],)
values
(@rowuno, 'Load', @col1, null, null, @col2, null, null, null, null, null, @col3,
null, null, null, null, null, null, null, null, null, @now)
update blablabla
set lastkey = @rowuno
where tbname = 'xxx'
-- Fetch Next Row
fetch next from empcode into @col1, @col2
end
close empcode
deallocate empcode
end
GO
---------
declare @col1 int
declare @col2 varchar(50)
declare @rowuno int
declare @now datetime
set @now = Cast(Cast(Year(getdate()) as char(4)) + '-' + Cast(Month(getdate()) as varchar(2))
+ '-' +Cast(Day(getdate()) as varchar(2)) as datetime)
begin
declare empcode CURSOR FOR
select distinct bla1, bla1, bla1 from xxx
open empcode
fetch next from empcode into @col1, @col2
while @@fetch_status = 0
begin
set @rowuno = (select lastkey from blablabla where tbname = 'xxx')
set @rowuno = @rowuno + 1
insert into aaa
([a],,[c],[d],[e],[f],[g],[h],
[DAYS_OVER_TERMS],[COMM_CR_LIMIT],[COMM_CR_RATE],,[j],[k],[k],
[m],[n],[o],[p],
,[r],)
values
(@rowuno, 'Load', @col1, null, null, null, @col2, null, null, null, null, @col3,
null, null, null, null, null, null, null, null, null, @now)
update blablabla
set lastkey = @rowuno
where tbname = 'xxx'
-- Fetch Next Row
fetch next from empcode into @col1, @col2
end
close empcode
deallocate empcode
end
GO
February 16, 2010 at 2:51 am
Please post the Create table statement and the Create index statement for the table and indexes involved. I dont want to make incorrect assumptions.
Also, why are you not using an identity column ?
Seems to me that you have created a lot of work to do the same job.
Additionally, you dont need a cursor here. It should be a simple insert .. select statement (once you have used an identity column for rownum)
February 16, 2010 at 3:11 am
Ok please could you write me the simplest way to insert rows from one table to another and also I have a rate_uno column that needs to be unique how would I update that? at the same time?
February 16, 2010 at 3:19 am
Heres some simple code to demonstrate an insert select..
Create table #t1
(
col1 char(1)
)
go
insert into #t1 values('A')
insert into #t1 values('B')
insert into #t1 values('C')
insert into #t1 values('D')
go
Create table #t2
(
t2Id integer identity,
col1 char(1)
)
go
insert into #t2(col1) select col1 from #t1
go
select * from #t2
As far as you rate_uno column goes, in which way does it need to be unique ?
What sets it apart from the ID column ?
February 16, 2010 at 3:19 am
If I do something like the below how would I get the rate_uno to update??
insert into xxx (rate_uno, a, b, c)
select distinct (select max(rate_uno)+1 from xxx), a, b, c from table
This brings error message like
Violation of PRIMARY KEY constraint 'xxx3'. Cannot insert duplicate key in object 'dbo.table'.
The statement has been terminated.
Please helpp
February 16, 2010 at 3:25 am
Rate_uno needs to be an incrementing and unique number e.g. max(rate_uno)+1.
How would I simply insert data and setting this column e.g. max(rate_uno)+1?
February 16, 2010 at 3:35 am
jyoti_bhatt (2/16/2010)
Rate_uno needs to be an incrementing and unique number e.g. max(rate_uno)+1.How would I simply insert data and setting this column e.g. max(rate_uno)+1?
Ill repeat my question from earlier , why are you not using an identity column ?
February 16, 2010 at 3:38 am
Sorry but I'm not sure what you mean?? Do you mean why I'm not using a PK? Rate_Uno is set as the PK.
I have one key as:
tablename3
2 Indexes as:
tablename0 (unique, non-clustered)
tablename3 (unique, non-clustered)
If that helps??
February 16, 2010 at 3:48 am
Relook at my example before.
See the t2Id column on the #t2 table ?
Thats an identity and is a unique incrementing integer.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply