Some Basic SQL Reminders

1. Send result of SELECT statement to xml output

select * from Table1

FOR XML auto, Type, Elements

If you choose to save the results to file, it will generate an xml file.

2. Try a transaction without commiting

BEGIN TRANSACTION

UPDATE M

SET IsLockedOut = 0

FROM aspnet_Membership M

INNER JOIN aspnet_Users U

ON M.UserId = U.UserId AND M.IsLockedOut = 1

ROLLBACK TRANSACTION

This will try the transaction and then rollback the changes, even if it is successful. Great for trying out statements to see if they work or not, without destroying the database.

3. UPDATE Syntax

UPDATE

Document

SET

CodeId = 22041

WHERE

DocumentID=25

4. INNER JOIN on an INSERT statement

INSERT INTO DocumentGroupSender(reportGroupid, retailerid)

SELECT

DG.id, S.id

FROM Document D

INNER JOIN DocumentVersion DV ON D.currentDocumentVersionId = DV.id

INNER JOIN Sender S ON S.documentVersionId = DV.id

INNER JOIN DocumentGroup DG ON RG.reportId = D.id AND DG.isRetailer = 1 AND DG.name = 'Other'

This allows an insert into a new table using existing values based on a condition (or 3 here).

5. UPDATE statement with an INNER JOIN

UPDATE DocumentGroup

SET documentId = DV.documentId

FROM DocumentGroup DG

INNER JOIN DocumentVersion DV on DG.documentVersionid = DV.id

This allows a table to be updated based on a condition.

6. INSERT data from one table column to a column in a different table

INSERT INTO DocumentGroupCategory(categoryid)

SELECT id

FROM Category

Handy for porting data over form specific columns instead of whole tables.

7. INNER JOIN syntax

SELECT F.FishName, M.MailCodeId, M.FishDescription

FROM Fish F INNER JOIN ColourCode M

ON F.FishId = M.FishId

AND F.Region = M.Region

JOIN FishFamily FF

ON FF.FishFamilyID = F.FishFamilyId

WHERE M.Region = 'CE'

Well, you never know when you might want to select specific colours and families of fish from certain regions..


8. INNER JOIN within a DELETE

DELETE A FROM Amphibian A
INNER JOIN Frog F
ON F.id = '22' AND F.amphibianId = A.amphibianId
WHERE amphibianId = '1'


The "DELETE A FROM.." part is really important.  If this is not included the query will fail.