|

SQL Server and Other Topics

rss

I got my start with SQL Server in 1997 with SQL Server 6.5 at BellSouth Cellular. From that point on I've had the opportunity to work on Versions 6.0 to 2014. I specialize in Performance Tuning, High Availability and SQL Development. Contact me through this site or through my Twitter handle @SQLDiver


You can find thousands of examples of how to use an OUTPUT clause in your T-SQL, but few show how to output specific columns, and let say you want to loop through the record set limited to the top 10 rows at a time (you'd probably limit to 10k or 100k, but for example) here you go:

The reason I may do this would be to limit the impact of locking in a production database for a table where you want to delete the rows and record the deletes.

 

DELETE TOP(10) tb

OUTPUT deleted.TableID, deleted.Field1, deleted.Field2, deleted.Field3, deleted.Field4

INTO [archive].[Table] (TableID, Field1, Field2, Field3, Field4)

FROM [dbo].[Table] tb