Hello, my name is Robert Pound and I am a product technologist at Quest. This video covers the generation of test data for SQL Server, using Quest Toad. For more information on this topic you can view the Help from the Help menu and peruse the different topics there, or you can visit www.quest.com for more information on this or other products.
So as you can see, I have Toad for SQL open and I've already made a connection to the database itself. So to generate data, you have a couple of different options. If I wanted to generate data for an entire database, I could come to File, New, and Data Generator.
And that would open up a tab that would allow me to either open an existing project or create a new one by selecting a database. And again, using the connections that I have, or I could create a new connection here. And I will select the database.
And now, in the new tab, we have a list of all the different objects associated with the database, and we have the connection information at the top. At the very top, we have a menu that has the file options for saving and opening projects, as well as exporting data. And we also have an Options menu. This allows you to affect some of the script information-- as far as running pre and post scripts, check constraints, global variables-- as far as setting the default row count for the generation, what to do for invalid data, default file location, et cetera. I will leave all this default for now.
And this is for generating, again, data from multiple tables. If we wanted to look at a single table, I could simply come over to the Object Explorer. And I have the production database selected. I could right click, and again choose Data Generator.
So now that we have the single object, the first thing that I usually do is look at the number of rows. So it may be important to create thousands of rows or just a few dozen rows, depending on what you're interested in doing. You can see, if I modify the number of rows for this specific table and then hit Refresh, the Preview pane at the bottom will change. So you can see the immediate effect.
So to the rows themselves, what happens in the background when you're choosing Data Generation is, Toad will go out and look at the data types for each one of the columns and then at the name of the column itself and give its best guess on what it thinks the type of data that you were interested in generating. For Location ID, it's given us an auto increment. For Name, it's given first name-- and that may not necessarily be what we want, especially considering this is a table about location.
So if I choose the dropdown menu and I go all the way up to the top-- and start collapsing all of these-- we can see that this was under the Personal Information, but this may be more of an address sort of thing. So if I select Address and expand out, we can start to do things like put in generated data about city names, or of states, countries, et cetera. I'm going to choose State, and you can see that it was refreshed as soon as I chose the dropdown. But again, you could choose Refresh here or here.
And again, depending on the data type itself, you have limited options as far as the dropdown. So obviously, you can't put a date/time in a column that is only accepting small ints. So you can modify some of the fields. You can add null values where applicable to give more realistic feel of the data.
But once you have the columns the way you want them, you can either export the data as we talked about in this menu, or we can generate the data now. You'll be prompted with a menu. You can directly run the script now, and you have the option of making it a background process. I'm going to choose the script option so we can see the final product.
And there you have it. This is the INSERT statement inserting the 10 rows of data. I hope this was helpful. Once again, for more information, you can visit www.quest.com. Have a great day.
04:52