SQL Blog of Zach Stagers

www.scratchbox.co.uk

Merge Statements

December 20th, 2010 Posted in SQL
Dec, 20 2010
No Comments »

Merge statements allow you to perform three different actions depending on the results of a table join. The Merge statement uses an inner join.

There are three possible boolean (yes or no) checks that can be used to determine which action you’d like to be taken. They Are:

When Matched Then – This will contain the statement that you’d like to execute when there is a match in both tables. Up to two “When Matched Then” clauses can be specified within a single Merge statement using an additional join statement added with an AND. When two “When Matched Then” statements are specified, you must make sure that one performs an Update, and the other a Delete statement. The second will only ever be executed when the first is not.

When Not Matched By Source – This section will contain the SQL that will be executed when there is a record held in the Target table, but not in the Source table.

When Not Matched By Target – This is the opposite of the above, and will contain the code executed when a record exists in the Source table, but not the Target table.

An example of the syntax may make the above clearer:

MERGER INTO Person P
USING Employees E ON E.EmployeeId = P.PersonId
WHEN MATCHED THEN
   UPDATE
   SET LastUpdated = GETDATE()
WHEN NOT MATCHED BY SOURCE THEN
   DELETE
WHEN NOT MATCHED BY TARGET THEN
   INSERT (Name, Age, EmployementStatus)
   VALUES (E.Name, E.Age, E.EmployementStatus);

Notice the semicolon (;) at the end of the statement. This is essentially for the statement to execute.

The above example will update the LastUpdated column in the Person table if there is a match in both the Person and Employees table.

When there isn’t a match in the Employees table (The Source) for a record that exists in the Person table (The Target), the record that’s in the Person table will be deleted.

When there isn’t a match in the Person table (The Target) for a record that exists in the Employees table (The Source), the record that’s in the Employees table will be inserted.

Each of the DML (Data Manipulation Langauge – Delete, Update, Insert, and Merge statements) can have the Output and Output Into clauses applied to them

,
Comment

Output Into – Audit Trails

December 19th, 2010 Posted in SQL
Dec, 19 2010
1 Comment »

Output & Output Into can be used to track the details of data that’s inserted, updated, or deleted.

Ouput is a “simplified” version of Output Into. Output will simply print the results to screen, where as Output Into will insert the results into a specified table.

These keywords have access to two sytem tables, inserted and deleted. The inserted table contains all data that is added, anything inserted into a table with an insert statement, or the new data added within an update statement. The deleted table holds the opposite, anything removed with a delete statement, or anything overwritten with an update statement.

The below examples show how to use the Output clause with a Delete, Insert, and Update Statement.

DELETE FROM Person
OUTPUT deleted.PersonId, deleted.Name INTO PersonAudit
WHERE PersonId = 73

INSERT INTO Person
OUTPUT inserted.PersonId inserted.Name INTO PersonAudit
VALUES('Zach Stagers')
UPDATE Person
SET Name = 'Zach Stagers'
OUTPUT inserted.PersonId, inserted.Name, deleted.Name INTO PersonAudit
WHERE PersonId = 88

With each of the above examples, remove INTO PersonAudit to use the simplified version mentioned earlier, and just print the results to screen.

With a little bit of planning and correct use of the Output Into clause, you have the ability to create a very useful audit trail. They’re frequently used within triggers to audit users SQL without the need for the extra syntax.

, , ,
Comment

Soundex – Overcome Spelling Issues

December 7th, 2010 Posted in SQL
Dec, 07 2010
2 Comments »

Spelling is a big issue of mine, I just can’t do it! The Soundex built in SQL function helps overcome any spelling issues by assigning a value to a varchar depending on the phonetics of the word.

For instance, people are constantly spelling my name incorrectly.. Zac, Zack, Zak.. it’s Zach!!

Passing the above varchar’s into the soundex function as below, will all return Z200 :

SELECT SOUNDEX('Zach')

This means that if someone emails me asking about an account for Zak Stagers, I can use the below SQL to find the correctly spelled account:

SELECT FirstName
FROM Users
WHERE SOUNDEX(FirstName) = SOUNDEX('Zak')

The above will return all accounts that have a Soundex value of Z200, allowing me to find the correct account even without the correct spelling

, , ,
Comment