How to prepare a fake database via SQL of a working database for testing Microsoft Dynamics GP & Development
We placed this question on EE but didn't related to what we're working in: Microsoft Dynamics GP. So if you see it repeated, it was a mistake. Nevertheless, we'll would greatly appreciate EE opinion of the MS Dynamics Zone.
We would like to test our VB.NET developments on database with real data but not affect the production database.
We've been tweaking with the idea of making a copy of the production database, rearrange all sensitive data as: last names, addresses and invoices, payments amounts.
The problem we're having is the algorithm or logic of relating masters tables and their transactions tables.
What we're looking for is to have the same structure from the production database so we can developed on it. In addition, for validating the apps, the values of agings, historicals, and also to have it square off with their related tables.
We would liek to go about using SQL script since our DB are in MS SQL.
Here's is an example of what we we're trying to do:
ACTUAL REAL DATABASE CONTENTS:
Customer Address Invoice Amt payment Amt
=================== =============== ========== ===========
Johnny Walker Red Comp. Corner Drive 124, LA 15,000.00 12,520.00
Big E LTD. Alba 9087, NY 250.00 250.00
Summers Inc Raton Hall, FL 4,569.00 100.00
FAKE DATABASE CONTENTS FOR DEVELOPING & TESTING:
Customer Address Invoice Amt payment Amt
=================== =============== ========== ===========
Johnny Walker Red Comp. Corner Drive 124, LA 15,000.00 12,520.00
Big E LTD. Alba 9087, NY 250.00 250.00
Summers Inc Raton Hall, FL 4,569.00 100.00
We would like to test our VB.NET developments on database with real data but not affect the production database.
We've been tweaking with the idea of making a copy of the production database, rearrange all sensitive data as: last names, addresses and invoices, payments amounts.
The problem we're having is the algorithm or logic of relating masters tables and their transactions tables.
What we're looking for is to have the same structure from the production database so we can developed on it. In addition, for validating the apps, the values of agings, historicals, and also to have it square off with their related tables.
We would liek to go about using SQL script since our DB are in MS SQL.
Here's is an example of what we we're trying to do:
ACTUAL REAL DATABASE CONTENTS:
Customer Address Invoice Amt payment Amt
=================== =============== ========== ===========
Johnny Walker Red Comp. Corner Drive 124, LA 15,000.00 12,520.00
Big E LTD. Alba 9087, NY 250.00 250.00
Summers Inc Raton Hall, FL 4,569.00 100.00
FAKE DATABASE CONTENTS FOR DEVELOPING & TESTING:
Customer Address Invoice Amt payment Amt
=================== =============== ========== ===========
Johnny Walker Red Comp. Corner Drive 124, LA 15,000.00 12,520.00
Big E LTD. Alba 9087, NY 250.00 250.00
Summers Inc Raton Hall, FL 4,569.00 100.00
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Reading your post again, I think that is the way to go, changing NAMES, ADDRESS, TELS, etc. But how can we go about ID codes?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Understood.
We can't make any purchae right now but We are working in the script right and think we have all the pertaining tables.
Based on your comments please advice in the following:
- any consideration we should have when changing ID?
- why scheme, logic or algorithm you used to create the
different ID code?
We can't make any purchae right now but We are working in the script right and think we have all the pertaining tables.
Based on your comments please advice in the following:
- any consideration we should have when changing ID?
- why scheme, logic or algorithm you used to create the
different ID code?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great idea! Use dex_row value to replace the employID. We can do that with all the custs, vendors, etc. But can you elaborate on your comment "use UPR00100 as your ID temp table."?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes it does; great info!
One lat question, why first change the transaction tables and then at the end the master table?
One lat question, why first change the transaction tables and then at the end the master table?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes very, thank you very much... We're working hard in the scripts in all tables.
ASKER
Excellent!
ASKER
To date, we have only limited to payroll info where we delete all transactions, telephones, address and history data. Unfortunately when developing/tasting, the programmer has to enter data in order to make it work
You are correct, I didn't change how we want to have it; this is how should be:
Customer Address Invoice Amt payment Amt
=================== =============== ========== ===========
Johnny Walker Red Comp. Corner Drive 124, LA 15,000.00 12,520.00
Big E LTD. Alba 9087, NY 250.00 250.00
Summers Inc Raton Hall, FL 4,569.00 100.00
FAKE DATABASE CONTENTS FOR DEVELOPING & TESTING:
Customer Address Invoice Amt payment Amt
=================== =============== ========== ===========
Johnny Walker Red Comp. xxxxxxxxx 25.00 20.00
Big E LTD. xxxxxxxxx 25.00 25.00
Summers Inc xxxxxxxxx 25.00 20.00
We need to do what you mention in your post about HR/Payroll data.
In our case, the Employee ID has the Employees name fully. That is if the name is JOHN J. SMITH, the employee code would be JOHNJSMITH.
I like the way you go about it, can you give more info?