Tuesday, December 18, 2007

The Same, But Different

Okay...  A short while ago I posted a little application I wrote called the MIIS Delta View Creator.  It's neat and clean and does what it says it does.  But I also have another method up my sleeve.  (Well, it might be up my sleeve if I kept my sleeve in the hard drive bay...)  Anyway, here's another version implemented as a SQL Management Studio template.

Create the template:

  • In SSMS, enable the Template Explorer (Ctrl + Alt +T)
  • If desired (and it is desired) create a new template folder.
    • Right click on the SQL Server Templates root folder and select "New" and then, yes...  "Folder"
    • Type the new name for the folder.  I suggest either "Rosencrantz" or "MIIS Custom", but this is all you.
  • Right click on your new template folder and create a new template.
    • I suggest any name other than, "New SQL Server Template".
    • I called mine, "Create Basic MIIS Delta View Components" and I have a reference to that in the comments of the script.
  • Now, right click your new template and select "Edit".
  • Paste this code into the query editor window and save the template.

Use the template:

  • In SSMS, double click the template.
    • A copy of it will open in a new query editor window.
    • If you want to edit the template itself, right click it and select "Edit".
  • With the new query editor window as the active window, press Ctrl + Shift + M.
  • Enter the appropriate value for each parameter and click "OK".
    • You now have a script that, when run, will create the necessary delta view components.

Okay...  enough with the bullets.

  • Maybe just one more...

The resulting script will create a basic delta view setup.  All that's necessary to get started is to have one existing table/view.  This table should represent the "Current" view of the data.  The "Original" table and the delta view will be created.  If there are objects with the specified names already in the database, they will be dropped!

The script also creates a couple of additional components:  A table copy stored procedure and a post process stored procedure.  These components are useful for the way we implement a lot of the MIIS functionality.  Read through the code and all will be revealed.  If you have questions, comments or suggestions, please feel free to post them here.

All I ask is: if my readers (yes, both of you) use this code, please note where you got it from in any altered versions that you mangle (create).

No comments: