Extract, Transform, and Load in Reverse
The first step is to create a view that limits the size of the largest transaction record volume. Using the Modulo operator, copy the results of the view to a dummy table that replaces the original table (without the sp_rename). Use the Data Transformation Services (DTS) to do the heavy lifting. DTS has the intelligence to move related database objects between databases so you don't have to manually do it yourself. Using the Northwind database, I'll walk you through developing the extract.
First, you need to set the scene for your export. Create a view of the lowest-level transactional table in your database (in the case of Northwind, that table is Order Details). As the lowest level of transactions, Order Details has the largest volume of data. To control the size of the export while preserving the relational integrity of the copy, create a view that selects only a portion of the transactions and use DTS to mind the relational details for you.
Create a New View
Open the Northwind Database and right click Views. You'll find a number of cool views on which you could build. For example, you could use the Order Details Extended view, but you play with enhancements only after the view details are created. For the purposes of this Solution and to ease the spoofing of the Order Details table, select New view.
The View Designer window opens with four design surfaces. In the top pane, right click to add Order Details to the view. Select All Columns. In the SQL pane, add the Where Clause as shown in Figure 1.
|Figure 1. The View Designer Window: Get a close-up view of the view designer window.|
Save the view as MiniOrderDetails and close the View Designer. If you return to Enterprise Manager and run the view, you'll see that instead of returning the entire Order Details table (2,155 rows), it returns only 22 rows. You have effectively chopped the volume of data down to one percent of the table's contents by using the Modulo operator in the Where clause, which also has the fortunate side effect of preserving all the parent records that belong to that Order Details' keys. You get a consistent picture of the relational chain but with only one percent of the detail in the view, yet you still have all the parent records that support the records in the Order Details table. Now you can use this view as the basis for creating a mini version of the Northwind database.