Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- begin tran
- if exists (select * from table with (updlock,serializable) where key = @key)
- begin
- update table set ...
- where key = @key
- end
- else
- begin
- insert table (key, ...)
- values (@key, ...)
- end
- commit tran
- begin tran
- update table with (serializable) set ...
- where kay = @key
- if @@rowcount = 0
- begin
- insert table (key, ...) values (@key,..)
- end
- commit tran
- MERGE INTO table_name WITH (HOLDLOCK) USING table_name ON (condition)
- WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
- WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])
- MERGE INTO table_name WITH (HOLDLOCK) USING table_name ON (condition)
- WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
- WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])
- IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)
- UPDATE [Table] SET propertyOne = propOne, property2 . . .
- ELSE
- INSERT INTO [Table] (propOne, propTwo . . .)
- ALTER PROCEDURE dbo.Merge_Foo2
- @ID int
- AS
- SET NOCOUNT, XACT_ABORT ON;
- MERGE dbo.Foo2 WITH (HOLDLOCK) AS f
- USING (SELECT @ID AS ID) AS new_foo
- ON f.ID = new_foo.ID
- WHEN MATCHED THEN
- UPDATE
- SET f.UpdateSpid = @@SPID,
- UpdateTime = SYSDATETIME()
- WHEN NOT MATCHED THEN
- INSERT
- (
- ID,
- InsertSpid,
- InsertTime
- )
- VALUES
- (
- new_foo.ID,
- @@SPID,
- SYSDATETIME()
- );
- RETURN @@ERROR;
- BEGIN;
- -- other operations
- SAVEPOINT sp1;
- INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
- -- Assume the above fails because of a unique key violation,
- -- so now we issue these commands:
- ROLLBACK TO sp1;
- UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
- -- continue with other operations, and eventually
- COMMIT;
- INSERT INTO MyTable (Key, FieldA)
- SELECT @Key, @FieldA
- LEFT OUTER JOIN MyTable
- ON Key = @Key
- WHERE Key IS NULL
- IF @@ROWCOUNT = 0
- BEGIN
- UPDATE MyTable
- SET FieldA=@FieldA
- WHERE Key=@Key
- IF @@ROWCOUNT = 0
- ... record was deleted, consider looping to re-run the INSERT, or RAISERROR ...
- END
- INSERT INTO table(Id, Description)
- SELECT @Id, @Description
- WHERE NOT EXISTS (SELECT NULL FROM table WHERE Id = @Id)
- DECLARE @RowExists bit
- SET @RowExists = 0
- UPDATE MyTable SET DataField1 = 'xxx', @RowExists = 1 WHERE Key = 123
- IF @RowExists = 0
- INSERT INTO MyTable (Key, DataField1) VALUES (123, 'xxx')
- INSERT INTO Sales(TransId, Status, Amount)
- VALUES(123, 'Pending', 20)
- ON DUPLICATE KEY UPDATE Status = 'Paid'
- INSERT INTO Sales(TransId, Status, Amount)
- VALUES(123, 'Pending', 20)
- ON DUPLICATE KEY UPDATE Status = 'Paid'
Add Comment
Please, Sign In to add comment