![]() ![]() Using Microsoft Excel, open the file containing the exported data (for example, c:\temp\export.xls), and optionally add one or more rows.įigure 10-5 shows the original file with one row added for the Antarctica region. If you do not see the NEW_REGIONS table, disconnect from NICK (right-click NICK in the Connections navigator and select Disconnect) and connect again, and expand the Tables node. In the Connections navigator, expand the Tables node under the NICK connection. The Script Output pane shows that the NEW_REGIONS table has been created. In the SQL Worksheet for the NICK connection, type the following:Ĭreate table new_regions ( region_id number primary key, region_name varchar2(25)) In SQL Developer, if you have not already created a database connection for NICK, create the connection. ![]() To accomplish these goals, follow these steps: (This example is trivial, and adding a row to the Excel file may not be typical, but it is presented merely to illustrate some capabilities.) This user wants to take the exported data, add one row in the Excel file, and import it into a new table that has the same column definitions as the REGIONS table. ![]() Figure 10-2 shows the result of these actions.Įxample: Importing Data from a Microsoft Excel FileĪssume that you wanted to import the data that was exported in "Example: Exporting Data to a Microsoft Excel File", into a new table that has the same column definitions as the original ( REGIONS) table.įor example, assume that you created a user named NICK following the instructions in "Example: Creating a User". On the Specify Objects page, click Lookup, then double-click the REGIONS table on the left to have it appear in a row in the bottom part of the page. On the Types to Export page, deselect Toggle All, then select only Tables (because you only want to export data for a table). (In the figure, this file is C:\temp\export.xls.) Save As location: Enter or browse to a desired folder on your local hard drive, and specify the file name for the. sql script file is generated (which will not happen in this example), it will not contain any CREATE statements, but only INSERT statements.įormat: Select xls to have the data saved to a Microsoft Excel. Optionally, click the REGIONS table in the Connections navigator, and examine the information under the Columns and Data tabs in the main display area.įigure 10-3 Export Wizard: Source/Destination Specifying Data Export Onlyĭescription of "Figure 10-3 Export Wizard: Source/Destination Specifying Data Export Only"Īccept the default values for the Source/Destination page options, except as follows:Įxport DDL: Deselect (uncheck) this option. The Script Output pane shows that the REGIONS table has been created and four rows have been inserted. In the SQL Worksheet for the NICK connection, type the the Run Script icon. ![]() If you have not already created a database connection for NICK, create the connection. To re-create the REGIONS table in the schema of user NICK by invoking the script in C:\temp\export.sql follow these steps using SQL Developer: This other schema can be an existing one or one that you create.įor example, assume that you created a user named NICK following the instructions in "Example: Creating a User". For this, the entire file is read from the disk and passed on as a string argument to the method ().Example: Importing Metadata and Data Using a Script FileĪssume that you wanted to re-create the REGIONS table that you exported in "Example: Exporting Metadata and Data for a Table", but in a different schema. The implementation for executing a full script file is straightforward. This enables us to test the parsing capabilities of the libraries discussed in this article. VALUES (2, 'Jane', 'Smith', 'IT', 60000.00) Īs we can see, the above file consists of a mix of block comments, single-line comments, blank lines, create table statements and insert statements. INSERT INTO employees (id, first_name, last_name, department, salary) nScript(new java.io.FileReader(path)) Īs evident in the above code, ScriptRunner gives the option to execute the script line by line as well as the full script in one go.īefore executing the SQL file, let’s take a look at it: - Create the employees table if it doesn't exist ScriptRunner scriptRunner = new ScriptRunner(connection) ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |