Readers' Tips and Tricks - 08 May 2000

Editor's Note: Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $50.

Find and Insert Missing Records

Need to find records that are in an update table but not in a production table, then insert those records into the production table? Check out the script in Listing 1. I use this T-SQL script, which doesn't contain cursors or nested select statements, to process updates that come to me as dumps of everything the company has in production, including new records.

The script compares the production table and the update table by examining their common unique identifying key, which should be indexed. The script produces a table with the keys of the rows that are in the update table but not in the production table, then uses those keys to populate the production table with only the missing records.

The script first builds the production and update tables to hold data for comparison, then creates a temporary table (#worktable) to hold missing record keys. After populating the production and update tables with the sample data, the script populates #worktable with the missing record keys, retrieves the missing records, populates the production table with the missing rows, and displays the now-complete production table.

The script ends by deleting the comparison production and update tables and #worktable; but make sure you don't drop your real production and update tables. You could also extend the script to remove from the production table records that no longer exist in the complete update table.

—Will Haney
[email protected]

Using Cursors to Perform Bulk Operations

Ken Spencer's SQL Server Secrets column "Refreshing Views" in SQL Server Magazine UPDATE (November 18, 1999) suggested a useful script. The script repeatedly calls system stored procedure sp_refreshview to automatically refresh multiple views in the database instead of refreshing them one by one. I've found that I frequently want to perform operations such as refreshing views, checking table data, creating SQL statements, rebuilding indexes, or changing the table structure for many objects at once. To perform such bulk operations, I keep a SQL cursor on hand as a template and change it as necessary.

The script in Listing 2 opens a cursor on the table specified in the SELECT statement—in this case, the sysobjects table—then loops through each record in the cursor and assigns the contents of the current name field to the @ObjectName variable. The script uses the @ObjectName variable to build an EXEC statement to perform the desired operation. This example refreshes all views that db_owner owns.

By changing the SELECT statement in the cursor, you can perform operations against any database objects you want, use a WHERE clause to restrict the result set, and even add an ORDER BY clause. You can also edit the cursor's EXEC portion to perform a wide range of operations against the objects the SELECT statement returns. For example, Listing 3 shows how you can change the cursor to add a Timestamp field to each table in the database. If you wanted to add the field to only 30 tables, you would simply change the SELECT statement to return only the tables you want to work with. And by replacing EXEC with PRINT, you can easily generate a SQL script, which you can save and execute later. With a little imagination, you can use this cursor technique to perform numerous time-saving operations.

—Clancy Gould
[email protected]

Expanding the Databases Node

Have you ever expanded the Databases node in Enterprise Manager, then waited, waited, and waited some more before SQL Server displayed the database list? One DBA reported twiddling his fingers for more than 2 minutes every time he expanded the Databases node to see a list of his 20 databases. What's the problem? The likely culprit is the Auto Close option. If you select this option, every time you expand the Databases node, SQL Server first has to open every database and verify whether you have access. Simply clear the Auto Close option, and enjoy the performance improvement.

—Yuval Peleg
[email protected]

Deleting Identical Rows

Here's an easy way to delete a duplicate row. Just specify Set Rowcount 1 before the delete statement. I'm very careful with production data, so I even surround this statement with a begin transaction statement and make sure I have the expected results before issuing the commit transaction statement. After you've deleted the identical row, don't forget to specify Set Rowcount 0, or every subsequent result set you request will contain only one row.

—Bret Mitchell
[email protected]

Changing T-SQL's Case in Query Analyzer

Changing T-SQL code in the Query Analyzer from upper to lower case, or vice versa, is simple. To convert code to uppercase, highlight the code, then press Ctrl+Shift+U. To change to lowercase, highlight the code, then press Ctrl+Shift+L.

—Al Noel
[email protected]

Backing Up Specific Tables

I often need to back up a couple of tables several times a day from a database of 2000 tables. But backing up an entire 2GB database is impractical when you need to save only 20MB from three or four tables.

My solution is to use Data Transformation Services (DTS), either manually or with a scheduled package, to copy the tables I need to back up from the original database to an empty dummy database. I then save the dummy database and drop all of its tables. If I need to restore the tables, I just restore the dummy database and run the inverse DTS operation to get them online instantaneously. You can also use the dummy database to test application changes.

—Sebastian Sajaroff
[email protected]