Setup a Workbench Database, Migrate Objects from Production and Modify the Workbench Database

By: Rick Dobson   |   Updated: 2023-04-05   |   Comments   |   Related: > Database Configurations

Problem

I am a SQL developer and I tend to use SQL data manipulation statements in my development projects, such as SELECT, SELECT INTO, INSERT, DELETE, and UPDATE. However, I seek assistance applying CREATE/DROP DATABASE and ALTER TABLE statements in a SQL development context. Please present a use case that empowers and motivates me to build richer, more robust solutions with SQL Server data definition language statements.

Solution

Sometimes developers need to set up an environment to test design changes to an existing application database even while the application remains in regular use. You can think of this type of activity as a workbench project. You need to be able to set up a workbench database and then test alternative design changes that meet the requirements for the changes with code that is easy to maintain and provides acceptable performance. At your convenience, you can migrate the workbench version of a modified object to the production version of a database. The use case example in this tip focuses on setting up the workbench database, migrating table and key constraint objects from the production version of a database to the workbench database, modifying a migrated table, and verifying that the table design and values change as intended. The two data main definition statements that will be used in the use case example are

• The CREATE DATABASE statement to originate a workbench database
• The ALTER TABLE statement to originate database objects within a workbench database; this tip illustrates the origination of tables and key constraint objects in the workbench database

Creating the Workbench

The workbench database name in this demonstration is named alter_examples. In data definition language, it is common to drop an object, such as a database, before attempting to create a fresh version of it. The following script illustrates how to invoke the drop database and create database statements to create a fresh workbench database named alter_examples.

• The script starts with a use statement that makes the master database the default database. Fresh databases are based on the model database in the master database.
• Next, the db_id function checks if an existing alter_examples database is present in the SQL Server instance. If yes, the script drops the existing alter_examples database before attempting to create a fresh version of the database.
• The script ends with a create database statement to create a fresh copy of the alter_examples database.

The sys view functions are a very popular way of querying for the existence of database objects. For that reason, the script includes a commented section that shows how to use the sys.databases view in place of the db_id function to check for the existence of an alter_examples database in the SQL Server instance.

-- create alter_examples database
use master
go
 
-- create a fresh version of alter_examples db with the db_id function
if db_id (N'alter_examples') is not null
drop database alter_examples
go
create database alter_examples;
go
 
/*
-- an alternate way to create a fresh version of the alter_examples db with sys.databases
if (select name from sys.databases where name = 'alter_examples') is not null
drop database alter_examples
go
create database alter_examples;
go
*/

Survey Objects to Copy from an Application Database to a Workbench Database

Before copying database objects from a production database to a workbench database, it is a good practice to survey the objects you are copying to the workbench. The following script accomplishes this goal.

• The use statement designates the name of the production database (DataScience) from which objects are copied to the workbench database (alter_examples)
• A declare statement instantiates the @tname1 and @tname2 local variables, which store the names of the table objects to be copied to the workbench database from the DataScience database
• The select statement after the declare statement displays text that serves as a title for other text in the SSMS Results tab from the batch of SQL statements
• The next select statement assigns values for the names of the table objects to be copied from the DataScience database to the workbench database
• The final two select statements
  • display from the sys.tables view the name and the object_id values for the two table names in @tname1 and @tname2
  • query the sys.key_constraints view to display name and object_id values for either of the two table names in @tname1 or @tname2
• The go keyword at the end of the script terminates the SQL batch for surveying objects to be copied from the DataScience database to the alter_examples database

-- display name and object_id values for two tables from DataScience database (a pre-existing db)
use DataScience
go
 
declare @tname1 nvarchar(128), @tname2 nvarchar(128)
 
select 'Objects for DataScience Database Survey' [Database Source]
 
-- assign values to @tname1 and @tname2
-- name of first table to add to workbench
-- name of second table to add to workbench
select 
   @tname1 = 'symbol_date', 
   @tname2 = 'yahoo_finance_ohlcv_values_with_symbol'  
 
-- display name and object_id for two table names
select @tname1 [first table name], @tname2 [second table name]
-- name and object_id for two tables
select name, object_id from sys.tables where name in (@tname1, @tname2)
 
-- display primary key constraints for two tables
select name, object_id, parent_object_id, type 
from sys.key_constraints 
where parent_object_id in 
(select object_id from sys.tables where name in (@tname1, @tname2))
go

Here is the output from the preceding script.

• The first pane in the results tab is for the title of the content from the script
• The second pane shows the values assigned to @tname1 and @tname2
• The third pane shows the name and object_id values for each table from the sys.tables view
  • The object_id value for the symbol_date table is 245627968
  • The object_id value for the yahoo_finance_ohlcv_values_with_symbol table is 613577224
• The fourth pane displays the name, object _ID, parent_object_id, and type values for the two tables from the sys.key_constraints view
  • There is just one primary key constraint; this is indicated by just one row of output from the query of the sys.key_constraints view
  • The row is for the symbol_date table
  • The parent_object_id value (245627968) from the sys.key_constraints view points to the object_id value for the symbol_date table object from the sys.tables view
  • The key constraint type is PK, which is for a primary key constraint
  • There are no foreign key constraints in the DataScience database at the time of this tip's preparation. However, if there were, a separate line of output would appear for each foreign key constraint

Copying Objects to a Workbench Database

The next SQL batch is for a script to populate the alter_examples workbench database with the two table objects and a key constraint object from the DataScience database surveyed in the preceding script.

• The SQL batch starts with a use statement that specifies the alter_examples database as the default database for the SQL batch
• Next, a declare statement instantiates the @tname1 and @tname2 local variables for this SQL batch; local variables do not propagate across SQL batches. Therefore, you must declare local variables in each batch that needs them
• The first select statement displays a title at the top of the Results tab from the SQL batch; the title is "'Objects for alter_examples database"
• The second select statement assigns values to @tname1 and @tname2; the next select statement displays the assigned values for the local variables
• Next, two conditional drop table statements remove the symbol_date and yahoo_finance_ohlcv_values_with_symbol tables from the workbench if they exist in the database from a prior run of the SQL batch (or for another reason)
• After that, two select into statements are used to populate the symbol_date and yahoo_finance_ohlcv_values_with_symbol tables in the alter_examples database from the corresponding tables in the DataScience database
  • The into clause of these statements relies implicitly on the default database for the SQL batch (alter_examples)
  • The from clause of these statements uses a three-part name to specify the data source; this allows the designation of the DataScience database as the source database for the tables copied to the alter_examples workbench database
• Next, a select statement with a from clause pointing at the sys.tables view is used to display in the Results tab name and object_id values for the symbol_date and yahoo_finance_ohlcv_values_with_symbol tables in the alter_examples database
• The next select statement tries to use the same code as in the preceding SQL batch to display any key constraints associated with the tables in the workbench database. The comment before the select statement indicates that the select statement fails to return any key constraint objects; the comment uses the word Oops to convey the failure. The failure is because no key constraints were added explicitly for either table
• There are two steps to correct for the missing primary key constraint
  • First, conditionally drop any primary key constraint from the alter_examples database with the name of the primary key constraint that you want to add
  • Second, invoke an alter table add key statement to explicitly add a primary key constraint pointing at the symbol_date table in the alter_examples database; you also need to specify the column name(s) on which the primary key is based
• The next-to-the-last statement in the SQL batch displays information about any key constraints for either of the tables in the alter_examples database
• The go keyword at the end of the script terminates the SQL batch

-- copy two tables from DataScience db
-- to alter_examples db with select into
-- and add primary key constraint for the symbol_date table
-- in the alter_examples db
use alter_examples
 
declare @tname1 nvarchar(128), @tname2 nvarchar(128)
 
-- display name and object_id values for two tables from alter_examples db
select 'Objects for alter_examples database' [Database Source]
select 
   @tname1 = 'symbol_date', 
   @tname2 = 'yahoo_finance_ohlcv_values_with_symbol'
 
-- display name and object_id for two table names in alter_examples db
select @tname1 [first table name], @tname2 [second table name]
 
-- drop tables from alter_examples db if they are already there
drop table if exists dbo.symbol_date
drop table if exists dbo.yahoo_finance_ohlcv_values_with_symbol
 
-- copy symbol_date and yahoo_finance_ohlcv_values_with_symbol tables
-- from DataScience db to alter_examples db
select *
into dbo.symbol_date
from DataScience.dbo.symbol_date
 
select *
into dbo.yahoo_finance_ohlcv_values_with_symbol
from DataScience.dbo.yahoo_finance_ohlcv_values_with_symbol
 
-- name and object_id for two tables
select name, object_id from sys.tables where name in (@tname1, @tname2)
 
-- display primary key constraints for two tables
-- Oops, we got the tables but not the pk constraint
select name, object_id, parent_object_id, type 
from sys.key_constraints 
where parent_object_id in 
(select object_id from sys.tables where name in (@tname1, @tname2))
 
-- use alter table to explicitly add fresh pk constraint
-- first drop the existing primary key constraint, then add primary key constraint
alter table [dbo].[symbol_date] drop constraint if exists [pk_symbol_date]
alter table [dbo].[symbol_date] add  constraint [pk_symbol_date] primary key clustered
(
   [symbol] asc,
   [date] asc
)
 
-- display primary key constraints for two tables
-- now, the pk constraint displays for symbol_date
select name, object_id, parent_object_id, type
from sys.key_constraints
where parent_object_id in
(select object_id from sys.tables where name in (@tname1, @tname2))
go

Here is the output from the preceding script.

• The first pane in the results tab is for the title of the content from the script
• The second pane shows the values assigned to @tname1 and @tname2
• The third pane shows the name and object_id values for each table from the sys.tables view
  • The object_id value for the symbol_date table is 933578364
  • The object_id value for the yahoo_finance_ohlcv_values_with_symbol table is 949578421
• As you may recall, the first attempt to display key values failed because there were no key constraints in the alter_examples database; this is why the fourth pane displays no values below the column heads
• The fifth pane displays the name, object _ID, parent_object_id, and type values for the two tables from the sys.key_constraints view
  • There is just one primary key constraint; this is indicated by just one row of output from the query of the sys.key_constraints view
  • It is for the symbol_date table
  • The object_id for the key constraint is 965578478
  • The parent_object_id value (933578364) from the sys.key_constraints view points to the object_id value for the symbol_date table object from the sys.tables view
  • The key constraint type is PK, which designates a primary key constraint

Updating a Table Object in a Workbench

One of the major reasons for creating and populating a workbench is to make changes to the objects in the workbench without altering a production database that needs to be available 24 hours a day (or at least during normal working hours). This tip section demonstrates how to add a new column to the symbol_date table and then populate the new column with fresh column values. After verifying that any changes you made to a workbench object functions properly, you can move the revised object from the workbench database to the production database.

• The SQL batch below commences with a use statement referencing the alter_examples workbench database as the default database
• Next, an alter table statement is used with a conditional drop column statement followed by an add command from within an alter table statement to add a new column named leveraged_or_unleveraged to the symbol_date table
• Then, the code creates and populates with data manipulation language statements a fresh temp table named #temp_with_new_column_value
  • The temp table has symbol and date column values from the workbench version of the symbol_date table
  • A case statement nested within a cast function populates the column values for rows in the new_column_value column of the temp table
    • The column value for a new_column_value row is unleveraged whenever the symbol value is DIA, QQQ, or SPY
    • When the symbol value for a row is UDOW, TQQQ, SPXL, then the new_column_value row is leveraged
    • Else the new_column_value row is null
  • An into clause for a select statement populates the #temp_with_new_column_value table with the select statement's results set
• An update statement revises the null values from the leveraged_or_unleverage column in the workbench version of the symbol_date table based on the new_column_value object from the #temp_with_new_column_value table
• A couple of select statements from before and after the update statement show the distinct symbol-leveraged_or_unleveraged tupples from the symbol_date table
• The script also includes some commented code for displaying in a side-by-side fashion all the rows in the symbol_date table from the DataScience database and all the rows from the symbol_date table from the alter_examples database. The alter_examples version of the table includes the leveraged_or_unleveraged column and its values that were never assigned to the original DataScience version of the table
• Finally, a go statement terminates the SQL batch

use alter_examples
go
 
-- add a fresh new column to symbol_date table in alter_examples db
alter table dbo.symbol_date
drop column if exists leveraged_or_unleveraged
go
alter table dbo.symbol_date
add leveraged_or_unleveraged nvarchar(20)
go 
 
-- create and populate #temp_with_new_column_value
-- with symbol and date values along with matching 
-- new_column_value
 
drop table if exists #temp_with_new_column_value
 
select 
 symbol 
,date
,
 cast(
 case
   when symbol in ('DIA', 'QQQ', 'SPY') then 'unleveraged'
   when symbol in ('UDOW', 'TQQQ', 'SPXL') then 'leveraged'
   else null
 end as nvarchar(20)) new_column_value
into #temp_with_new_column_value
from symbol_date
 
-- list distinct symbol and leveraged_or_unleveraged column value pairs
-- before update statement
select distinct symbol, leveraged_or_unleveraged from symbol_date
 
-- update symbol_date.leveraged_or_unleveraged
-- with #temp_with_new_column_value.new_column_value
update
     symbol_date
set
     symbol_date.leveraged_or_unleveraged = #temp_with_new_column_value.new_column_value
from
     dbo.symbol_date 
     inner join #temp_with_new_column_value
     on symbol_date.symbol = #temp_with_new_column_value.symbol and
    symbol_date.date = #temp_with_new_column_value.date;
 
--
--select distinct symbol, leveraged_or_unleveraged from symbol_date
 
-- show result of updated column values from alter_examples and DataScience databases
 
-- list distinct symbol and leveraged_or_unleveraged column value pairs
-- after update statement
select distinct symbol, leveraged_or_unleveraged from symbol_date
 
/*
-- optionally list all rows in symbol_date tables
-- from DataScience and alter_examples databases
select ds.*, ae.*
  from [alter_examples].[dbo].[symbol_date] ae
  join datascience.dbo.symbol_date ds
  on ae.symbol = ds.symbol
  and ae.date = ds.date
*/
go

Here's the output from the preceding script. It shows two window panes in the Results tab.

• The top pane shows the select statement output from before the update statement. Notice leveraged_or_unleveraged column values are NULL
• The bottom pane shows the select statement output from after the update statement. Notice that all leveraged_or_unleveraged column values contain string values of either leveraged or unleveraged

This change to the symbol_date change was not very substantial in scope. Nevertheless, I did manage to create an error on my first try at writing the code. Certainly, for more complicated changes, it would be easy for even experienced SQL professionals to make an error. After the required changes are verified in the workbench, you can replace the former version of the symbol_date table from the DataScience database with the updated version of the symbol_date table from the workbench database.

Next Steps

After reading this tip, the next step is to decide if you want hands-on experience with the code samples in this article. You can get the code you need for hands-on experience from the code windows in the tip. However, if you want to run the code exactly as described in the tip, you need the symbol_date and yahoo_finance_ohlcv_values_with_symbol tables.

• The source data and the T-SQL script for importing the source data to the symbol_date table are available from the download for a prior tip named "SQL Server Data Mining for Leveraged Versus Unleveraged ETFs as a Long-Term Investment". This tip also includes the source code for the symbol_date table's primary key constraint.
• The source data and the T-SQL script for importing the source data for the yahoo_finance_ohlcv_values_with_symbol table are available from the download for a prior tip called "A Framework for Comparing Time Series Data from Yahoo Finance and Stooq.com". For a discussion about the T-SQL script for importing the source data into a SQL Server table, read the "Transferring CSV files from Yahoo Finance to a SQL Server table" subsection.

Another approach is to adapt the code excerpts provided in this tip to create your own workbench database; this approach removes the need to copy CSV files from a prior tip and then load the contents of the CSV files into a SQL Server table. With this approach, you only need to copy one or more database objects from your production database to your newly created workbench database. Next, change the workbench objects according to your requirements; you must update the object names from those used in this tip to the names of your production database objects. Finally, replace your production database with the modified objects from the workbench database.

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips