Here's the step-by-step process, which uses the Pubs database. First, open Enterprise Manager, Query Analyzer, then choose the Pubs database from the drop-down list at the top of the window. Note that the SELECT INTO operation is part of the process. Therefore, you have to set the database to accept SELECT INTO, either from Enterprise Manager or from Query Analyzer. In Enterprise Manager, right-click Pubs, select Properties, select the Options tab, and select the Select Into/Bulkcopy check box. From Query Analyzer, run the following statement:
USE pubs
GO
sp_dboption 'pubs', 'SELECT into/bulkcopy', true
Now, create and populate a table to hold the prefixes for the 375 new table names:
CREATE TABLE MyPrefix (PrefixCode CHAR(1) )
GO
From Enterprise Manager, open the table MyPrefix (to see the new table, you might have to refresh the display) and input the values A, B, C, D, and E to create five rows in the table. Close the table window. Then, create and populate a table to hold the region values:
CREATE TABLE MyRegion(RegionID CHAR(2) )
GO
From Enterprise Manager, open the MyRegion table and input values 01 through 75, thereby creating 75 rows in the table. Close the table window. Now, create the template tables; for this exercise, you can use five tables from the Pubs database:
SELECT * INTO Table_A FROM authors WHERE 0=1
SELECT * INTO Table_B FROM publishers WHERE 0=1
SELECT * INTO Table_C FROM discounts WHERE 0=1
SELECT * INTO Table_D FROM stores WHERE 0=1
SELECT * INTO Table_E FROM sales WHERE 0=1
Next, run the following code to create a set of 375 names for the new tables by cross-joining the prefix table (MyPrefix) with the table that contains the list of region numbers (MyRegion):
SELECT PrefixCode + RegionID AS 'NewName' INTO #MyTableName
FROM MyPrefix CROSS JOIN MyRegion
GO
The resultset that you get should look like the data that Figure 1 shows. You can create temporary tables because you'll use T-SQL to create all inserts from this point on.
Now, you can build and populate a temporary table to hold the 375 SELECT...INTO code strings, as Listing 1 shows. All you have to do to produce the necessary code to create the 375 tables is first run the following query:
SELECT * FROM #MyString
GO
Then, display the resultset in text mode, as Figure 2 shows. Highlight the resultset, copy it to the Windows Clipboard (press CTRL+C), paste it back into the query window, and run the 375 SELECT INTO statements. In a few minutes, the 375 new tables will reside in the Pubs database.
Time-Saving Solutions for Repetitious Code
Until I received this project assignment, I hadn't considered using T-SQL to create T-SQL statements. I've never needed to. Neither had I been able to give a positive answer to the question, "What good is a cross join?" In one simple project assignment, I've been able to do both. You can use this technique in your job when your manager or client asks you to develop sets of repetitious code. I know that in the future, I'll look more carefully at my assignments and employ this technique whenever I can.
End of Article
Prev. page
1
[2]
next page -->
