TSQL Output Tables

Using output tables when inserting multiple rows

If you need to do processing on individual rows when inserting multiple rows into a database table I'd normally use a cursor to achieve what I want.

Instead you can use the OUTPUT instruction to place the details into a temporary table...

DECLARE @TEMP TABLE (NEWID INT, OUTID INT)
INSERT INTO DESTTABLE (VAL1, VAL2, OLDVALUE)
OUTPUT INSERTED.ID, INSERTED.OLDVALUE INTO @TEMP
SELECT VAL1, VAL2, ID
FROM SRCTABLE

This can also be used for UPDATE and DELETE but it's important to remember that on INSERT commands there must be a column for the old value to be inserted

TSQL XML Manipulation

How to change XML data via TSQL

Modifying XML data via TQL can be done using the following .modify commands...

To insert a new node/attribute...

UPDATE MyTable SET MyXml.modify('insert <item>new</item> as first into (/root)[1]')
UPDATE MyTable SET MyXml.modify('insert <item>new</item> as last into (/root)[1]')
UPDATE MyTable SET MyXml.modify('insert <item>new</item> after (/root/item[1])[1]')
UPDATE MyTable SET MyXml.modify('insert <item>new</item> before (/root/item[1])[1]')
UPDATE MyTable SET MyXml.modify('insert attribute attr {"new"} into (/root/item[1])[1]')

To change text/attribute in a node...

UPDATE MyTable SET MyXml.modify('replace value of (/root/item[1]/text())[1] with "new"')
UPDATE MyTable SET MyXml.modify('replace value of (/root/item[1]/@attr)[1] with "new"')

To delete a node/attribute/text...

UPDATE MyTable SET MyXml.modify('delete /root/item[1]')
UPDATE MyTable SET MyXml.modify('delete /root/item[1]/@attr')
UPDATE MyTable SET MyXml.modify('delete /root/item[1]/text()')

Align SQL Login to User

How to re-align a SQL Login to a Database User

In SQL Server there are two distinct security level when it comes to individual users... one at the server level, the other at the database level.

If a database is restored from a different location, then despite the there being a server login and database user with the exact same name, they are different objects and do not therefore align with each other.

Instead of removing either and re-implementing, simply use the following...

ALTER USER OrphanUser WITH LOGIN = correctedLoginName;