SQL executes and returns pre/post-execution results

1. Insert the data and return the inserted data:

INSERT into TESTTB (province,city) output inserted. Province, inserted. City VALUES (' Guangdong ', ' Shenzhen ')

2, similarly, the deletion of data is the same, but the use of deleted table.

Delete from TESTTB output deleted.* where id=1

3. Together: Returns both pre-and post-update data:

Update testtb SET province = ' Hunan ', city= ' Chenzhou ' OUTPUT ' I came from (pre-update) ' + DELETED. Province+deleted. City as [before], ' I'm from (updated) ' + inserted.province+inserted.city as [after] WHERE id=1

4. You can also save the returned results in a table variable, which is useful when deleting data and inserting deleted data into the history table

DECLARE @temp tabletable (Idint, Provincevarchar (), Cityvarchar () DELETEFROMTESTTB OUTPUT Deleted.*[em AIL Protected]>4select*[email protected]

--Returns the value before update ppdai_jr_shop.dbo.GeneratorUniqueNo SET suffix=suffix+2 OUTPUT inserted.suffix WHERE prefix= ' ppdtk '-- Returns the updated value of update ppdai_jr_shop.dbo.GeneratorUniqueNo SET suffix=suffix+2 OUTPUT deleted.suffix WHERE prefix= ' PPDTK '

SQL executes and returns pre/post-execution results