CODEWITHSUNDEEP
sqloracle-databaseoracle11g
artemis
artemis

Reputation: 7281

INSERT INTO failing on SQL Fiddle

I am trying to create a SQL Fiddle for creating a test database.

My query is below:

-- schema
CREATE TABLE tblInputs (
    Type VARCHAR(256),
    Order_No VARCHAR(256),
    Planned_No VARCHAR(256),
    Purchase_No VARCHAR(256),
    Direction VARCHAR(256)
);

CREATE TABLE Requirements (
    Order_No VARCHAR(256),
    Planned_No VARCHAR(256),
    Req_ID VARCHAR(256),
    Req_No VARCHAR(256)
);

CREATE TABLE ReqRep (
    Req_ID VARCHAR(256),
    Req_No VARCHAR(256),
    Rep_ID VARCHAR(256)
);

CREATE TABLE Replenishments (
    Rep_ID VARCHAR(256),
    Order_No VARCHAR(256),
    Planned_No VARCHAR(256),
    Purchase_No VARCHAR(256)
);


-- data
INSERT INTO tblInputs (Type, Direction, Order_No, Planned_No, Purchase_No)

SELECT 'Purchase', NULL, NULL, 'PO9000124798', 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908851', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908852', NULL, 'PLUS' FROM DUAL 
UNION ALL
SELECT 'Planned', NULL, 'PL191908853', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908854', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908855', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908853', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Build', 'O103916639', NULL, NULL, 'MIN' FROM DUAL
UNION ALL
SELECT 'Production', 'O103962037', NULL, NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Production', 'O103933200', NULL, NULL, 'PLUS' FROM DUAL

INSERT INTO Requirements (Order_No, Planned_No, Req_ID, Req_No)
SELECT NULL, 'PL191908851', 'ABA', '36' FROM DUAL UNION ALL
SELECT NULL, 'PL191908852', 'CC',  '93' FROM DUAL UNION ALL
SELECT NULL, 'PL191908853', 'BBA', '27' FROM DUAL UNION ALL
SELECT NULL, 'PL191908854', 'EWE', '42' FROM DUAL UNION ALL
SELECT NULL, 'PL191908855', 'WWW', '13' FROM DUAL UNION ALL
SELECT NULL, 'PL191908856', 'EEE', '33' FROM DUAL UNION ALL
SELECT NULL, 'PL191909922', 'GFW', '99' FROM DUAL UNION ALL
SELECT NULL, 'PL191910022', 'GFT', '23' FROM DUAL UNION ALL
SELECT NULL, 'PL192010120', 'THE', '54' FROM DUAL UNION ALL
SELECT 'O103962037',  NULL, 'BDD', '37' FROM DUAL UNION ALL
SELECT 'O103933200',  NULL, 'DFA', '63' FROM DUAL UNION ALL
SELECT 'O103547812',  NULL, 'ADS', '45' FROM DUAL UNION ALL
SELECT 'O103547415',  NULL, 'DWQ', '94' FROM DUAL UNION ALL
SELECT 'O103654787',  NULL, 'QZX', '96' FROM DUAL UNION ALL
SELECT 'O103214217',  NULL, 'NFD', '20' FROM DUAL UNION ALL
SELECT 'O103215320',  NULL, 'GBV', '33' FROM DUAL UNION ALL
SELECT 'O106212219',  NULL, 'ERQ', '22' FROM DUAL UNION ALL
SELECT 'O103215320',  NULL, 'MRP', '11' FROM DUAL

INSERT INTO ReqRep (Req_ID, Req_No, Repl_ID)
SELECT 'ABA', '36', '7736' FROM DUAL UNION ALL
SELECT 'CCC', '93', '6686' FROM DUAL UNION ALL
SELECT 'BBA', '27', '5710' FROM DUAL UNION ALL
SELECT 'EWE', '42', '7634' FROM DUAL UNION ALL
SELECT 'WWW', '13', '9393' FROM DUAL UNION ALL
SELECT 'EEE', '33', '8442' FROM DUAL UNION ALL
SELECT 'GFW', '99', '5758' FROM DUAL UNION ALL
SELECT 'GFT', '23', '5988' FROM DUAL UNION ALL
SELECT 'THE', '54', '6748' FROM DUAL UNION ALL
SELECT 'BDD', '37', '7123' FROM DUAL UNION ALL
SELECT 'BDD', '37', '7124' FROM DUAL UNION ALL
SELECT 'BDD', '37', '7125' FROM DUAL UNION ALL
SELECT 'BDD', '37', '7126' FROM DUAL UNION ALL
SELECT 'DFA', '63', '7125' FROM DUAL UNION ALL
SELECT 'ADS', '45', '5855' FROM DUAL UNION ALL
SELECT 'DWQ', '80', '9419' FROM DUAL UNION ALL
SELECT 'QZX', '96', '5748' FROM DUAL UNION ALL
SELECT 'NFD', '20', '7055' FROM DUAL UNION ALL
SELECT 'ERQ', '22', '7736' FROM DUAL UNION ALL
SELECT 'MRP', '11', '7736' FROM DUAL UNION ALL
SELECT 'GBV', '33', '9999' FROM DUAL

INSERT INTO Replenishments(Rep_ID, Order_No, Planned_No, Purchase_No)
SELECT '7736', NULL, NULL, 'PO9000124799' FROM DUAL UNION ALL
SELECT '6686', NULL, NULL, 'PO9000124800' FROM DUAL UNION ALL
SELECT '5710', NULL, NULL, 'PO9000124801' FROM DUAL UNION ALL
SELECT '7634', NULL, NULL, 'PO9000124802' FROM DUAL UNION ALL
SELECT '9393', NULL, NULL, 'PO9000124803' FROM DUAL UNION ALL
SELECT '8442', NULL, NULL, 'PO9000124804' FROM DUAL UNION ALL
SELECT '5758', NULL, NULL, 'PO9000124805' FROM DUAL UNION ALL
SELECT '5988', NULL, NULL, 'PO9000124806' FROM DUAL UNION ALL
SELECT '6748', NULL, NULL, 'PO9000124807' FROM DUAL UNION ALL
SELECT '7123', 'O103654787', NULL, NULL FROM DUAL UNION ALL
SELECT '7124', 'O103214217', NULL, NULL FROM DUAL UNION ALL
SELECT '7125', 'O103215320', NULL, NULL FROM DUAL UNION ALL
SELECT '7126', 'O106212219', NULL, NULL FROM DUAL UNION ALL
SELECT '7125', 'O103215320', NULL, NULL FROM DUAL UNION ALL
SELECT '5855', NULL, 'PL192010120', NULL FROM DUAL UNION ALL
SELECT '9419', NULL, 'PL121122221', NULL FROM DUAL UNION ALL
SELECT '5748', NULL, 'PL272634123', NULL FROM DUAL UNION ALL
SELECT '7055', NULL, 'PL983002032', NULL FROM DUAL UNION ALL
SELECT '9999', NULL, NULL, 'PO9000124806' FROM DUAL UNION ALL
SELECT '1111', NULL, NULL, 'PO9000124806' FROM DUAL

However, I am getting an error of:

ORA-00933: SQL command not properly ended

I am simply trying to create a few tables for use in a later SO post. The query up to this point works successfully:

-- schema
CREATE TABLE tblInputs (
    Type VARCHAR(256),
    Order_No VARCHAR(256),
    Planned_No VARCHAR(256),
    Purchase_No VARCHAR(256),
    Direction VARCHAR(256)
);

CREATE TABLE Requirements (
    Order_No VARCHAR(256),
    Planned_No VARCHAR(256),
    Req_ID VARCHAR(256),
    Req_No VARCHAR(256)
);

CREATE TABLE ReqRep (
    Req_ID VARCHAR(256),
    Req_No VARCHAR(256),
    Rep_ID VARCHAR(256)
);

CREATE TABLE Replenishments (
    Rep_ID VARCHAR(256),
    Order_No VARCHAR(256),
    Planned_No VARCHAR(256),
    Purchase_No VARCHAR(256)
);


-- data
INSERT INTO tblInputs (Type, Direction, Order_No, Planned_No, Purchase_No)

SELECT 'Purchase', NULL, NULL, 'PO9000124798', 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908851', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908852', NULL, 'PLUS' FROM DUAL 
UNION ALL
SELECT 'Planned', NULL, 'PL191908853', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908854', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908855', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Planned', NULL, 'PL191908853', NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Build', 'O103916639', NULL, NULL, 'MIN' FROM DUAL
UNION ALL
SELECT 'Production', 'O103962037', NULL, NULL, 'PLUS' FROM DUAL
UNION ALL
SELECT 'Production', 'O103933200', NULL, NULL, 'PLUS' FROM DUAL

It fails on the second INSERT INTO statement.

I have reviewed this post and this post but could not figure out a solution.

Upvotes: 1

Views: 201

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133390

You need semicolon for each query 

    INSERT INTO tblInputs (Type, Direction, Order_No, Planned_No, Purchase_No)

    SELECT 'Purchase', NULL, NULL, 'PO9000124798', 'PLUS' FROM DUAL
    UNION ALL
    SELECT 'Planned', NULL, 'PL191908851', NULL, 'PLUS' FROM DUAL
    UNION ALL
    SELECT 'Planned', NULL, 'PL191908852', NULL, 'PLUS' FROM DUAL 
    UNION ALL
    SELECT 'Planned', NULL, 'PL191908853', NULL, 'PLUS' FROM DUAL
    UNION ALL
    SELECT 'Planned', NULL, 'PL191908854', NULL, 'PLUS' FROM DUAL
    UNION ALL
    SELECT 'Planned', NULL, 'PL191908855', NULL, 'PLUS' FROM DUAL
    UNION ALL
    SELECT 'Planned', NULL, 'PL191908853', NULL, 'PLUS' FROM DUAL
    UNION ALL
    SELECT 'Build', 'O103916639', NULL, NULL, 'MIN' FROM DUAL
    UNION ALL
    SELECT 'Production', 'O103962037', NULL, NULL, 'PLUS' FROM DUAL
    UNION ALL
    SELECT 'Production', 'O103933200', NULL, NULL, 'PLUS' FROM DUAL7
    -- here
    ;


    INSERT INTO Requirements (Order_No, Planned_No, Req_ID, Req_No)
    SELECT NULL, 'PL191908851', 'ABA', '36' FROM DUAL UNION ALL
    SELECT NULL, 'PL191908852', 'CC',  '93' FROM DUAL UNION ALL
    SELECT NULL, 'PL191908853', 'BBA', '27' FROM DUAL UNION ALL
    SELECT NULL, 'PL191908854', 'EWE', '42' FROM DUAL UNION ALL
    SELECT NULL, 'PL191908855', 'WWW', '13' FROM DUAL UNION ALL
    SELECT NULL, 'PL191908856', 'EEE', '33' FROM DUAL UNION ALL
    SELECT NULL, 'PL191909922', 'GFW', '99' FROM DUAL UNION ALL
    SELECT NULL, 'PL191910022', 'GFT', '23' FROM DUAL UNION ALL
    SELECT NULL, 'PL192010120', 'THE', '54' FROM DUAL UNION ALL
    SELECT 'O103962037',  NULL, 'BDD', '37' FROM DUAL UNION ALL
    SELECT 'O103933200',  NULL, 'DFA', '63' FROM DUAL UNION ALL
    SELECT 'O103547812',  NULL, 'ADS', '45' FROM DUAL UNION ALL
    SELECT 'O103547415',  NULL, 'DWQ', '94' FROM DUAL UNION ALL
    SELECT 'O103654787',  NULL, 'QZX', '96' FROM DUAL UNION ALL
    SELECT 'O103214217',  NULL, 'NFD', '20' FROM DUAL UNION ALL
    SELECT 'O103215320',  NULL, 'GBV', '33' FROM DUAL UNION ALL
    SELECT 'O106212219',  NULL, 'ERQ', '22' FROM DUAL UNION ALL
    SELECT 'O103215320',  NULL, 'MRP', '11' FROM DUAL
    -- here
    ;


    INSERT INTO ReqRep (Req_ID, Req_No, Repl_ID)
    SELECT 'ABA', '36', '7736' FROM DUAL UNION ALL
    SELECT 'CCC', '93', '6686' FROM DUAL UNION ALL
    SELECT 'BBA', '27', '5710' FROM DUAL UNION ALL
    SELECT 'EWE', '42', '7634' FROM DUAL UNION ALL
    SELECT 'WWW', '13', '9393' FROM DUAL UNION ALL
    SELECT 'EEE', '33', '8442' FROM DUAL UNION ALL
    SELECT 'GFW', '99', '5758' FROM DUAL UNION ALL
    SELECT 'GFT', '23', '5988' FROM DUAL UNION ALL
    SELECT 'THE', '54', '6748' FROM DUAL UNION ALL
    SELECT 'BDD', '37', '7123' FROM DUAL UNION ALL
    SELECT 'BDD', '37', '7124' FROM DUAL UNION ALL
    SELECT 'BDD', '37', '7125' FROM DUAL UNION ALL
    SELECT 'BDD', '37', '7126' FROM DUAL UNION ALL
    SELECT 'DFA', '63', '7125' FROM DUAL UNION ALL
    SELECT 'ADS', '45', '5855' FROM DUAL UNION ALL
    SELECT 'DWQ', '80', '9419' FROM DUAL UNION ALL
    SELECT 'QZX', '96', '5748' FROM DUAL UNION ALL
    SELECT 'NFD', '20', '7055' FROM DUAL UNION ALL
    SELECT 'ERQ', '22', '7736' FROM DUAL UNION ALL
    SELECT 'MRP', '11', '7736' FROM DUAL UNION ALL
    SELECT 'GBV', '33', '9999' FROM DUAL
    -- here
    ;


    INSERT INTO Replenishments(Rep_ID, Order_No, Planned_No, Purchase_No)
    SELECT '7736', NULL, NULL, 'PO9000124799' FROM DUAL UNION ALL
    SELECT '6686', NULL, NULL, 'PO9000124800' FROM DUAL UNION ALL
    SELECT '5710', NULL, NULL, 'PO9000124801' FROM DUAL UNION ALL
    SELECT '7634', NULL, NULL, 'PO9000124802' FROM DUAL UNION ALL
    SELECT '9393', NULL, NULL, 'PO9000124803' FROM DUAL UNION ALL
    SELECT '8442', NULL, NULL, 'PO9000124804' FROM DUAL UNION ALL
    SELECT '5758', NULL, NULL, 'PO9000124805' FROM DUAL UNION ALL
    SELECT '5988', NULL, NULL, 'PO9000124806' FROM DUAL UNION ALL
    SELECT '6748', NULL, NULL, 'PO9000124807' FROM DUAL UNION ALL
    SELECT '7123', 'O103654787', NULL, NULL FROM DUAL UNION ALL
    SELECT '7124', 'O103214217', NULL, NULL FROM DUAL UNION ALL
    SELECT '7125', 'O103215320', NULL, NULL FROM DUAL UNION ALL
    SELECT '7126', 'O106212219', NULL, NULL FROM DUAL UNION ALL
    SELECT '7125', 'O103215320', NULL, NULL FROM DUAL UNION ALL
    SELECT '5855', NULL, 'PL192010120', NULL FROM DUAL UNION ALL
    SELECT '9419', NULL, 'PL121122221', NULL FROM DUAL UNION ALL
    SELECT '5748', NULL, 'PL272634123', NULL FROM DUAL UNION ALL
    SELECT '7055', NULL, 'PL983002032', NULL FROM DUAL UNION ALL
    SELECT '9999', NULL, NULL, 'PO9000124806' FROM DUAL UNION ALL
    SELECT '1111', NULL, NULL, 'PO9000124806' FROM DUAL
    -- here
    ;

Upvotes: 4

Related Questions