Hello All,

            I need to insert values in a table so that if a duplicate field comes for a record, it gets overwritten on the previous field and so on.
I dont have to use unique key constraints to avoid duplicate entry of data.

Any idea how to proceed....

Regards
Monica

Hi Monica!

You can use an instead of trigger for this case. But, one thing must be kept in mind that triggerd is fired once for a single INSERT/UPDATE/DELETE statement.

In this case, I am taking example of INSTEAD OF INSERT trigger.
------------------------------------------------------
-- Example of a Table (tbl_01)
CREATE TABLE tbl_01 ( id int , name varchar(20))
go

CREATE trigger trg_01 on tbl_01 instead of insert
as
declare @x int
declare @nam_old varchar(20)
declare @nam_new varchar(20)

select @x=id , @nam_new=name from inserted
if exists ( select id from tbl_001 where id=@x )
    Update tbl_001 set name=@nam_new where id=@x
else
    insert into tbl_001 select * from inserted

------------------------------------------------------
This will add new row and if id column is same, this would overwrite to the name column.

But, This will work for the last row only if you use a insert statement something like this -

INSERT INTO TBL_01
SELECT 2 , 'JACK'
UNION ALL
SELECT 3 , 'MAC'

Then, this example will work for MAC only.

Although, this demo can be modified to perform with same logic, but you may need a loop to perform it or a cursor.

But, if such insert statements are not used, this demo will do.

Reply soon ..

- Som Dutt
http://somdutt.blogspot.com

Here is an example that will work for multi row as well as single row inserts
CREATE TABLE tbl_01 ( id int , name varchar(20))
go

CREATE trigger trg_01 on tbl_01 instead of insert
as
UPDATE tbl_01 set name = i.name
FROM tbl_01 t
JOIN INSERTED i
ON t.id = i.id

INSERT into tbl_01
select i.*
FROM tbl_01 t
RIGHT JOIN INSERTED i
ON t.id = i.id
WHERE t.id IS NULL


David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

Thanks all.....i will come back with the results!