Syntax Sugar in SQL Server 2005

1. Using OUTPUT clause:

DECLARE @NewKeys TABLE (
  [Key1]    UNIQUEIDENTIFIER NOT NULL,
  [Key2]    UNIQUEIDENTIFIER NOT NULL
);

UPDATE [XTable]
SET [Key2] = NEWID()
  OUTPUT inserted.[Key1], inserted.[Key2]
  INTO @NewKeys
WHERE [Key2] IS NULL;

Only updated rows go to @NewKeys table, of course a temporary or even a regular table can be used instead of table variable. No need for triggers.

2. Using JOIN with UPDATE:

UPDATE [x]
SET [x].[Key1] = [y].[Key1]
FROM [XTable] AS [x] INNER JOIN [YTable] AS [y]
ON [x].[Key2] = [y].[Key2]

Advertisement