Forum Stats

  • 3,768,297 Users
  • 2,252,772 Discussions
  • 7,874,520 Comments

Discussions

bulk table creation using union

raj2k8mca
raj2k8mca Member Posts: 13
edited Mar 20, 2020 2:22AM in SQL & PL/SQL

Hi Experts,

    please provide me  select query to create an table output like this?

 

Table_namecolumn_name
create tableREGIONS(COUNTRY_ID
COUNTRY_NAME)
create tableCOUNTRIES(REGION_ID
LOCATION_ID
STREET_ADDRESS)
create tableLOCATIONS(POSTAL_CODE
CITY
STATE_PROVINCE
COUNTRY_ID
REGION_ID
REGION_NAME
create tableDEPARTMENTS(DEPARTMENT_ID
DEPARTMENT_NAME
MANAGER_ID
LOCATION_ID
create tableEMPLOYEES(EMPLOYEE_ID
EMPLOYEE_ID
START_DATE
END_DATE)
John ThortonBEDE

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond
    edited Mar 19, 2020 10:15PM

    Hi,

    raj2k8mca wrote:Hi Experts, please provide me select query to create an table output like this?Table_namecolumn_namecreate tableREGIONS(COUNTRY_IDCOUNTRY_NAME)create tableCOUNTRIES(REGION_IDLOCATION_IDSTREET_ADDRESS)create tableLOCATIONS(POSTAL_CODECITYSTATE_PROVINCECOUNTRY_IDREGION_IDREGION_NAMEcreate tableDEPARTMENTS(DEPARTMENT_IDDEPARTMENT_NAMEMANAGER_IDLOCATION_IDcreate tableEMPLOYEES(EMPLOYEE_IDEMPLOYEE_IDSTART_DATEEND_DATE)

    So, you want a query, which is some code that gets stuff out of tables.

    Are the table- and column names stored in a table (or maybe more than one)?  Post CREATE TABLE and INSERT statements for that table (or those tables).  See the Forum FAQ:

    If the table- and column names are not stored in a table, you can write a query that selects literals from the dual table, but it would be much simpler just to write the output you want in a text editor.

    John Thorton
  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy
    edited Mar 20, 2020 12:09AM

    Do you already have those tables in some Oracle database? If the answer is yes, then use the facilities od the dbms_metadata paclkage. That would mean calling the procedure dbms_metadata.get_ddl for each table for which you wish to extract. RTM on that.

    Other ways of doing somehting more or less as you said wold be using exp and imp utilities or expdp and impdp. Or using the facilities of sqldeveloper or some oter tool to export and import tables with data or without data.

    If you do not have those tables anywhere, as long as you are not giving any logic on how those table names, column names and data types are to be deduced from some data found somewhere in the database, then the only way is just to write by hand each create table statement to create the tables as you need them to be.

    By the way: until now I've never heard of "bulk table creation". If there is such a thing, please enlighten me!

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,581 Red Diamond
    edited Mar 20, 2020 2:22AM

    What about column data types, constraints, physical attributes (e.g. index table as oppose to a hash table), and so on?

    Please explain the WHY? behind your question. Just what business requirement are you trying to meet?

    BEDE