primary key...
hi,
i have a small problem.. it goes like this..
i have a table with one column set to primary key..
i am populating that primary key column with data from another table..
as we know, while a duplicate value is encountered, it will throw an exception..
now, can anyone suggest a method which does not throw this exception and noramally continue with the program , along with discarding the duplicate data...
is it possible to do with exception handling??
thanks in advance,
Marcia
[532 byte] By [
Marcia] at [2007-11-20 5:27:08]
# 1 Re: primary key...
Hi all.
Exception handling depends on kind of db you're using.
A general way to avoid this problem is to check if exists a record with the key values before execute the INSERT command; you can do this using a simple SELECT Count() query.
# 3 Re: primary key...
You can achieve it using temporary table or directly working with destination table
1) Create unique index on destination table "with ignore_dup_key"
create unique index indu on dest(manempid,empid,nation) with ignore_dup_key
2) Insert into destination from base table
insert temp
select * from mang
Thanks & Regards
Jonam
# 4 Re: primary key...
Can you post more information about the query you use to populate the table?
Also the database schema will be useful.
If you add multiple records and one fails, the whole insert operation will fail.
you can use (where not exists) to avoid the duplicates. This depends on how your tables look.
hspc at 2007-11-9 13:47:50 >
# 5 Re: primary key...
can u pls suggest how to use Stored Procedures to solve this problem?
i am using SQL Server as my database..
Well.
If you have to do this operation once (or very rarely) 007jonam's solution seems good.
Otherwise I suggest to develop a stored procedure that read records from the source table and write them into destination table; another important point is what happen when an error raises, as hspc said. Do you want to ignore it or prefer rollback the entire transaction?
Maybe using a stored procedure you'll have more flexibility to solve your problem.
