Menu
I'm just getting started with Visual Studio Database projects and loving the data generation plans I can create with it. However, in one of my projects I need to populate a lookup table with specific values. Since a foreign-key relationship exists between the lookup table and another table, I can't remove the lookup table from the data generation plan without removing the other table from the plan. But then I can't generate any data for the other table.
How can I either a) specify the exact data I want the generator to use when adding data to the lookup table, or b) have the generator not add new values to the table but instead use values that already exist?
Thanks!
830
Here is the SQL code for setting up this example. No data included: CREATE TABLE links_type_lookup ( name character varying(16) PRIMARY KEY ); CREATE TABLE links ( type character varying(16) NOT NULL REFERENCES links_type_lookup(name), name character varying NOT NULL, url character varying NOT NULL );
A lookup table is an array of data that maps input values to output values, thereby approximating a mathematical function. Given a set of input values, a lookup operation retrieves the corresponding output values from the table.
This type of lookup table works with databases and unloads data from them by using SQL query. Database lookup table reads data from the specified database table. The key which serves to search records from this lookup tables is the " where = ? [and ...] " part of the query.
A lookup table is nothing but a 'lookup' it give values to referenced table (it is a reference), it is used at the run time, it saves joins and space in terms of transformations.
Here's my take on Consistent Data Generation in Visual Studio 2008
The data generation tool in Visual Studio 2008 Data Edition is a great tool for populating your database with meaningless information to use in your unit tests, but when it comes time to do integration testing it's often important to have your data generation plan recreate a consistent dataset in key tables (like the lookup tables used in foreign keys which are often mirrored as Enums in your C# or VB.Net solutions). Fortunately, the data generation tool includes the sequential data-bound generator. This generator selects records from the specified data source and uses the results to populate your table.
So how do we make use of this? In our database solutions, we include two databases - the actual database we're working on, and a datageneration database. For the tables we need to consistenly populate, we duplicate the schema & table in the datageneration database (minus any indexes/keys/constraints/triggers, etc.), and then use the post-deployment script for that database to create the desired records. To reduce duplication of the populate scripts, the post-deployment script for the real database points to the datagenration populate script by a relative path. This also means that these tables will have the same records in them whether you have just deployed the database, or just run the data generation plan - which makes life easier for everyone on the team.
Full details here
150
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
