Toad for DB2 has some really good features for working with data for both DB2 and z/OS and DB2 LUW for developers and DBAs. Any time you have a data grid, and right now, I've selected a table and on the right-hand side, I've selected the data tab so the data grid or the contents of the rows of that table are shown. You could interact that grid in many various ways.
So if I right-click on the grid, I could easily and quickly export the grid contents to an Excel file. It's going to prompt me and ask me if I want to read all the rows of the table. I'm going to say yes.
And what that does is open up an Excel file, write to it, then close it, and you have the option of displaying that. So the Excel file gets created rather quickly, and you can see the column headers become headers in the Excel file sheet itself.
You can export it, like any data grid, into various other formats as well, using the Export Wizard. So if you right-click anywhere in the data grid and select the Export Wizard, the Wizard comes up and allows you to select various different output formats. I'm going to select my comma separated values format right now.
And as you can see, there are many options that you could specify as you are walking through this Wizard, such as, you know, using column names for a header row, and including the SQL statement, checking resources that are needed. You could get very granular and unselect columns that you might not want to export.
And you could specify the location, whether or not you want to add a daytime suffix of the file name or overwrite the file. I'm going to just give it a unique file name with an "a" at the end of it. Click Next. Click Next. Click Finish.
And Toad actually generates this new comma separated value file and I'm just going to open that up in a different application so we can see what it looks like. Because if you double-clicked it and opened it up in Excel, it would look like an Excel sheet when in fact it's not. It's actually a delimited file. So very nice, very nice type of file that Toad can create for you. All right, that's the file.
And what we're going to do now is we're going drive that Export Wizard one more time. So I'm just going to drive it and going to create a SQL script. So this actually is going to generate insert statements for each row of the table that you're exporting.
And here, you have the ability to-- like, if you wanted to create an insert script that might be used in a different table, to populate data in a different table, you could override the table name here. But by default, I'm going to take all these existing values by default, and just click Next. And give it a unique name here again. And Toad will generate the file.
I'm going to open up the file containing that. And we'll see that, again-- I'm gonna open that file directory up, and open up that file. In Toad, we could see that the file that was generated, it's a rather large file, but it's got all these INSERT statements. And it's got the values [INAUDIBLE] so basically, here's a way, if you wanted to use this, to create INSERT statements for all the existing rows of your table. And that's the Export Wizard.
There is an Import Wizard as well. I've got a team. I'm going to quickly create a clone of this table. And it'll be an empty table, and I'll show you how this works.
All right, a football team clone. And I'm just going to create this, so now we're going to have an empty-- should have an empty table. And what I'm going to do is just quickly export the values from this existing team's table-- just run it to, and export-- just going to export to an Excel file.
Click on this, open the containing folder so I know where it's at. So I'm going to read that file next. All right? So now, what I'm going to now, is send this, right-click on this and send this to the Import Wizard.
And so the Import Wizard is going to prompt me for a file name. And I'm going to just, basically, add that file that I just created, which is right here. So the most recent file here. This is the file.
So I basically selected that file once the Import Wizard came up. And it does a little sampling of that Excel file where it populates this grid, shows you some of the data, lets you select things such as, you know, are empty fields null, which column to start at because you don't necessarily have to start importing into your table from the beginning of an Excel spreadsheet. You could do it in the middle if you wanted to.
So it gives you some options here. You walk through. It gives you a sample of what it thinks the column values are going to be as it's going to be imported. It allows you to map things. By default, it maps by position. You can map by name, so if your columns were ordered differently in your table than they were in your Excel spreadsheet, you could modify those.
It allows you to select which table-- by default, the table I selected is going to be used. You could truncate the table, which DB2 would remove rows from the table before they're inserted, using this Insert Wizard. And in here, you could add additional files and tables and execute those all in one execution of the Import Wizard.
But for this example, we're going to go through one Import Wizard with one file to
09:39