Sunday, March 11, 2012

Apply insert duplicated key record

Hi~
When insert, just insert if it is a new item or update it if duplicated. The update operation should be summing or averaging. Anyone help?

INSTEAD OF INSERT Trigger
-Check for dupe
--If dupe, increment
--Else INSERT

Adamus

|||

Thank you for reply.

But would you give me some sample query?

|||

1. Validate if the data availabile in the Table

2. If Yes , execute the Update command

3. If No, execute the Insert command

(ex)

if Exists (Select * From SomeTable Where SomeColumn=SomeValue)

Update

Set

SomeValueColumn = SomeValueColumn + New Value

Where

SomeColumn=SomeValue

Else

Insert Into SomeTable values (...)

|||

Thank you for your help.
Here is more specific case.

CREATE TABLE mytable
(
id1 int,
id2 int,
value1 int,
PRIMARY KEY(id1,id2)
);

CREATE TRIGGER mytrigger ON mytable
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS(SELECT * FROM INSERTED I,mytable M
WHERE I.id1=M.id1 AND I.id2=M.id2)
UPDATE mytable SET value1 = value1 + 100
ELSE
INSERT INTO mytable SELECT id1,id2,value1 FROM INSERTED
END

BULK INSERT mytable FROM 'mydatafile_1.dat' WITH (FIRE_TRIGGERS);(1)
BULK INSERT mytable FROM 'mydatafile_2.dat' WITH (FIRE_TRIGGERS);(2)
BULK INSERT mytable FROM 'mydatafile_3.dat' WITH (FIRE_TRIGGERS);(3)

I want insert a record if new item, update it by adding new item value to the original value.

Suppose the 'mydatafile_1.dat' has all new items(1).
'mydatafile_2.dat' has some new items and some duplicated items(2)
and 'mydatafile_3.dat' has all new items(3).

Only (1) and (3) are applied correctly. (2) is updated all of record but never inserted the new items.
It seems that the validation part should be repaired.
And, I want to change 100 to the new inserted value.

No comments:

Post a Comment