Generating insert scripts from current database values

Generating insert scripts from current database values

How can I generate insert scripts from current database values? It should dynamically generate insert statements for all columns of all tables, without asking for table names or column names, using Oracle Data Dictionary tables.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Use the data dictionary view DBA_TAB_COLUMNS. Write a procedure that queries for OWNER, TABLE_NAME and COLUMN_NAME (ordered by OWNER, TABLE_NAME and COLUMN_ID). The proc should loop through all rows of the query and concatenate the values together to form the first part of the insert statement (INSERT INTO <table> (<column list>) VALUES ). Then, as you loop through and get to the end of one table, you would have an interior loop that would select * from the table you just built the INSERT for and concatenate all the values into the values clause. Once you've got the whole INSERT built, write it out to a file (use UTL_FILE) or some other method of your choosing to save the created statement.

This is just the idea for you to follow; you'll obviously need to work through the actual code yourself.

For More Information


This was first published in December 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top