SQLTeam.com | Weblogs | Forums

INSERT or append


#1

Hello,

I have to insert a record in a table like this script:

insert into Notes (name, surname, dateref, UserNote)
values ('Bob', 'Fedd', GETDATE(), 'Double questionarie');

but if is already present a record for these "name" and "surname",
I have to append the string 'Double questionarie'
to the already present value in the UserNote field.

How can I accomplish this task?

Thanks a lot in advance.

Luigi

PS
I'm using SQL Server 2008 R2, so some features are not available.


#2
BEGIN TRANSACTION
INSERT INTO Notes (name, surname, dateref, UserNote)
SELECT 'Bob', 'Fedd', GETDATE(), 'Double questionarie'
WHERE NOT EXISTS
(
    SELECT *
    FROM Notes
    WHERE name = 'Bob'
          AND surname = 'Fedd'
)
IF @@ROWCOUNT = 0 -- No insert, record already exists?
BEGIN
    UPDATE U
    SET Notes = COALESCE(Notes + '. ', '') + 'Double questionarie'
    FROM Notes AS U
    WHERE name = 'Bob'
          AND surname = 'Fedd'
IF @@ROWCOUNT <> 0 RAISERROR(... ) -- Something bad happened! Perform ROLLBACK
END
COMMIT

Various discussion points on what, if any, locks to use between the INSERT attempt and the UPDATE ...

Or you could use MERGE statement


#3

P.S. If UPDATE is likely to be MORE common than INSERT I would swap the order of the statements (UDPATE first, if ZeroRows THEN INSERT) for better performance


#4

Perfect, thank you very much Kristen.

Luigi


#5
IF EXISTS(SELECT 1 FROM dbo.Notes WHERE name = 'Bob' AND surname = 'Fedd')
BEGIN
    UPDATE dbo.Notes
    SET UserNote = ISNULL(UserNote, '') + 'Double questionarie'
    WHERE name = 'Bob' AND surname = 'Fedd';
END /*IF*/
ELSE
BEGIN
    INSERT INTO dbo.Notes (name, surname, dateref, UserNote)
    VALUES('Bob', 'Fedd', GETDATE(), 'Double questionarie');
END /*ELSE*/

#6

Is there (more of!!) a risk of a race-condition?