Toad Data Point – Create Tables and Import data with the Import Wizard

For the second time in as many Toad Data Point (TDP) demos I have had a prospect express interest in the ability to perform a ‘SELECT INTO’ (you may recognize this syntax or something similar depending on what DB platforms you work with) type operation for moving schema and data from an Excel object to Oracle (or any other platform) in one fell swoop. 

As an example, SQL Server users are used to having the ability to create a new table from values in another table in a single command. This is accomplished using the previously mentioned SELECT INTO operation like this:

SELECT *
INTO dbo.BI_DEPT_TABLE
FROM HumanResources.Department

The result is that a new table called BI_DEPT_TABLE is created with the same schema AND data as the source table ‘Department’

So with that example in mind, I want to make it clear that TDP makes this type of functionality available even across platforms. To demonstrate I have outlined the workflow below to help educate any existing users or evaluators. To clarify, the use case describes what is about to happen: 

SE CASE:  Using TDP, select schema and data information from an existing Excel db object (source) and insert it into a new or existing table in another DB platform (target being Oracle in this example) resulting in behavior similar to DBMS platform specific ‘SELECT INTO’ style operations:

1. Right click on Source Excel Table and choose Generate SQL | To Clipboard | Select Statement 
   
    
2. Switch to Oracle Target connection in the dropdown list of connections (or connect first if not connected already). 
   
    
3. Choose Tools | Import | Import Wizard | Next | Add Query… 
    
4. Paste the query obtained in step 1 above and configure the Import wizard as shown below using the preview button to ensure that the expected dataset is to be inserted, then click Next 
   
    
5. Choose the radio button to create ‘A single new table’ in the Oracle Schema of our choice with a new name (CUST_INFO_XLSX_IMPORT in this example) then specify constraints and datatypes if desired: 
   
    
6. Option here to add additional sources of data 
   
     
7. Option to defer execution to a later time 
   
    
8. SUCCESS!! 
   
    
9. Confirm creation of new table schema and data contents in the Schema Browser