12

I have created multiple table in oracle xe 11g database and i have saved the script for each table in different .sql file. But i need to create all tables at once using single .sql file. I tried to run below script but it is creating only once table at once.

CREATE TABLE ACCOUNT_DETAILS_TB 
(
  CUSTOMER_ID VARCHAR2(20) NOT NULL 
, ACCOUNT_ID VARCHAR2(20) NOT NULL 
);

CREATE TABLE ADDRESS_DETAILS_TB 
(
  ACCOUNT_ID VARCHAR2(20) NOT NULL 
, ADDRESS_ID VARCHAR2(20) NOT NULL 
);
vashishth
  • 2,751
  • 4
  • 38
  • 68
  • SQL statements need to be terminated with a `;` –  Oct 08 '13 at 06:54
  • i tried by adding the semicolon, but if i run the script using command prompt then only it is creating two table for me. – vashishth Oct 08 '13 at 09:26
  • Your script only contains two `CREATE TABLE` statements. Why do you think it should create more? –  Oct 08 '13 at 09:29
  • its miscommunication ;). i meant only through command prompt i am able to create two table, but if i try the same thing in sql developer it is creating only one table at once. – vashishth Oct 08 '13 at 09:42
  • 1
    In SQL Developer you need to use "Run Script" (not "Run Statement") if I'm not mistaken. But I am confused: you are clearly using Oracle and SQL Developer yet you accepted an answer that is for SQL Server and SQL Server Management Studio (which is **not** able to talk to an Oracle database) –  Oct 08 '13 at 09:56
  • i tried with run script button also. And i accepted the answer for Sql Server since my colleague has tested that. – vashishth Oct 08 '13 at 10:03
  • How can your colleague test an Oracle script using SQL Server? –  Oct 08 '13 at 10:05
  • he is working on sql server :P he tested the script to create multiple table same way we do in oracle. – vashishth Oct 08 '13 at 10:06

2 Answers2

21

You need to separate the create table scripts with / or end the command with ;, Try like this,

CREATE TABLE ACCOUNT_DETAILS_TB ( CUSTOMER_ID VARCHAR2(20) NOT NULL , ACCOUNT_ID VARCHAR2(20) NOT NULL )
/
CREATE TABLE ADDRESS_DETAILS_TB ( ACCOUNT_ID VARCHAR2(20) NOT NULL , ADDRESS_ID VARCHAR2(20) NOT NULL )
/

OR

CREATE TABLE ACCOUNT_DETAILS_TB ( CUSTOMER_ID VARCHAR2(20) NOT NULL , ACCOUNT_ID VARCHAR2(20) NOT NULL );

CREATE TABLE ADDRESS_DETAILS_TB ( ACCOUNT_ID VARCHAR2(20) NOT NULL , ADDRESS_ID VARCHAR2(20) NOT NULL );
Dba
  • 6,511
  • 1
  • 24
  • 33
  • 2
    It would be really nice and consistent with the other statements if [tag:sql] could interpret `create table (...), table2 (...), table3 (...)` as it interprets the `VALUES` clause of `INSERT` statement, for instance. –  Jan 26 '19 at 17:14
-6

I ran this script by changing the VARCHAR2 to NVARCHAR in SQL Server and found there are 2 tables created in the database in the name of

  • ACCOUNT_DETAILS_TB1
  • ADDRESS_DETAILS_TB2

Here is the screenshot of my database and the 2 tables created are rounded.

Screen shot of my database

Tapan kumar
  • 6,719
  • 1
  • 24
  • 25
  • 3
    And how does a screenshot for a SQL Server tool answer a question that is clearly marked as Oracle? –  Oct 08 '13 at 06:50