Showing posts from September, 2017

Quick Reminder: Transact-SQL OUTPUT Clause

The OUTPUT clause is a useful tool for getting values just inserted, updated, or deleted from a table. You'll find several uses for the OUTPUT clause on your own but here's a few examples to get you started. Getting the Identify Value of a Just-inserted Row You can use the OUTPUT clause to get the value of an identify column for a newly inserted row. Traditionally, you'd have to jump through a hoop or two to get the value and in some cases that value couldn't be relied upon to be accurate. (For instance, if several updates were occurring at the same time, you might get the identify column value of another insertion -- not the one you performed.) -- example #1 CREATE TABLE #BrgTest ( [TestID] int identity primary key not null, [Name] varchar (50), [Description] varchar (255) ); INSERT INTO #BrgTest ([Name], [Description]) OUTPUT INSERTED.[TestID], INSERTED.[Name], INSERTED.[Description] VALUES (' Pancake ', ' A goofy dog ') DROP TA

Quick Reminder: How to Disable and Enable All Foreign Key Constraints in Database

This is one of those things I don't do that often (it just doesn't seem like a good idea) so I always have to look it up. To disable all your constraints, use the following: EXEC  sp_msforeachtable  "ALTER TABLE ? NOCHECK CONSTRAINT all" To endable all constraints, use the following: EXEC  sp_msforeachtable  "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"