Here are a few useful code fragments that I use all the time.
You see too many Rows Affected Messages.
First, NOCOUNT. If you don’t want SQL Server to return the Row Count
(20 row(s) affected)
after you execute a query, here is the code:
— Turn off the rows affected message
SET NOCOUNT ON
Once you have executed your queries, you can enable the row count again
SET NOCOUNT OFF
ANSI Warnings interrupt your inserts
Next, do you get annoying ANSI errors when inserting data? You can remove those warnings using one simple command.
SET ANSI_WARNINGS OFF
If there is a failure, the inserts will fail. However, now you can skip those warnings and proceed with the inserts.
Finding and Dropping Temp Tables
Lastly, when working with true temp tables, the ones starting with ## or #, the old standard will not work. This is because Temp tables live in the database TempDB, not your current DB. SO, sysobjects won’t have the table info you’re looking for. The firs t snippet is what you would use for a standard table in your CURRENT database.
-- Won't Work for #temp tables! -- Will only work for regular tables that exist in your current database. IF EXISTS (SELECT * FROM sysobjects WHERE NAME='##tmpTable') BEGIN Truncate table ##tmpTable DROP TABLE ##tmpTable END
The strategy changes just a bit to find and drop a temp table. Instead of searching in your current database, you’re going to have to look and see if an object exists in TempDB. To do this, we will use the OBJECT_ID command. The OBJECT_ID command will return the ID for the requested object, if it exists. If OBJECT_ID returns a number, we know the temp table exists. Here is the script:
-- This script will delete temp tables IF OBJECT_ID('tempdb..##tmpTable') IS NOT NULL BEGIN Truncate table ##tmpTable DROP TABLE ##tmpTable END
The temp table is now gone and ready to be recreated, as needed.