June 25, 2014 at 6:29 am
Hi there,
We have a massive list of 15-20 tables in the database which is loaded with data on a daily basis and I am trying to build a static test data by just extracting some 100 rows for each of the tables however since there is referential integrity constraints between the tables, im wondering what approach do I need to take or start in extracting the right data that can then be loaded into the test database for testing with reduced volume. Please advise how to go about it.
PS:We have data dictionary in excel but no diagram.
June 25, 2014 at 6:56 am
1) You can diagram from SSMS. Right-click on the Database Diagrams folder in the Object Browser.
2) SSMS Tools Pack utility for SSMS has a tool to generate insert statements. Isn't free, but it is a GREAT tool!
3) Vyas Kondreddi has an old but still useful and free script to generate inserts.
http://vyaskn.tripod.com/code.htm
4) As for the FKs and the like, you may want to consider using tSQLt testing framework to do what you want. It can build copies of your tables that have all of the FKs removed (and other nice things like all columns nullable, no triggers, etc) so you no longer have to worry about such things when you just want testing.
5) If you want to maintain FK relationships among all your data you can determine what filters get you that and use those with 2 or 3 above.
6) Red-Gate has a Data Generator product that may be able to create FK-appropriate data for you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 26, 2014 at 2:00 am
Thanks a lot for your response. I will try the http://vyaskn.tripod.com/code/generate_inserts_2005
tday however I missed saying another important part of this exercise which is I need to basically create spoofed data files which can be reused for multiple test scenarios , so once I get the data right in all the tables based on referential integrity ( needed because the cube wouldnt process if there is no referential integrity ) and then export the data from the tables into flat files , to test the the new BI framework which has been revamped . Red gate data generator is not allowing me to connect and its a trail version . So is there any other way u think this can be achieved easily??
June 26, 2014 at 4:58 am
Once you hand-craft the data and get it the way you want you can:
a) just save off the insert scripts you created to build said data
or
b) use util_generate_inserts to script out inserts
Once you have those you simply need to have a DELETE script and then run your INSERTs in the correct order to refresh the data in the tables.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply