CODEWITHSUNDEEP
sqloracle-databaseinsert
MrPickles
MrPickles

Reputation: 949

0 rows inserted - How can I fix this?

I an trying to INSERT multiple rows into an SQL, Oracle table though SQL Developer v3.0.04

the Database was set-up by Uni so I don't know what version it is.

after looking on-line I have come up with the code below but it will not INSERT any data. I have tested the insert with just one row and that is OK. What am I missing?

Insert all 
  Into Patient Values
    ('101', '1 house', Null, 'Kingston', 'Surrey', 'KT1 1XX', '10/jan/1980', 'm', 01452987456)
  Into Patient Values
    ('102', '2 egg rd', 'vail', 'guildford', 'Surrey', 'GU1 1LL', '05/dec/1985', 'm', 01452987456)
  Into Patient Values
    ('103', '6 station rd', Null, 'guildford', 'Surrey', 'GU1 2XX', '15/may/1990', 'f', 01452987456)

Select * from Patient;

Upvotes: 1

Views: 4275

Answers (3)

archangel
archangel

Reputation: 21

Replace this query [ Select * from Patient; ] with Select * from dual;

dual is a virtual table which is not existing in our schema but it can be existed as a part of Oracle perspective in RAM not in our storage

Insert all Into Patient Values ('101', '1 house', Null, 'Kingston', 'Surrey', 'KT1 1XX', '10/jan/1980', 'm', 01452987456) Into Patient Values ('102', '2 egg rd', 'vail', 'guildford', 'Surrey', 'GU1 1LL', '05/dec/1985', 'm', 01452987456) Into Patient Values ('103', '6 station rd', Null, 'guildford', 'Surrey', 'GU1 2XX', '15/may/1990', 'f', 01452987456)

Select * from dual;

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231851

INSERT ALL has two different uses. One is to insert different sub-sets of selected columns into a table. The other is to direct rows into different according to certain criteria. In both cases the data comes from a SELECT clause rather than from VALUES. See the examples in the documentation.

Normally, you would simply write multiple INSERT statements potentially in a single PL/SQL block. Something like

begin
  Insert Into Patient Values
    ('101', '1 house', Null, 'Kingston', 'Surrey', 'KT1 1XX', '10/jan/1980', 'm', 01452987456);
  Insert Into Patient Values
    ('102', '2 egg rd', 'vail', 'guildford', 'Surrey', 'GU1 1LL', '05/dec/1985', 'm', 01452987456);
  Insert Into Patient Values
    ('103', '6 station rd', Null, 'guildford', 'Surrey', 'GU1 2XX', '15/may/1990', 'f', 01452987456);
end;
/

If you really want to do this in a single SQL statement, you can do an INSERT ... SELECT but that's generally going to be more complex than using three separate statements.

insert into patient
  select *
    from (select '101' id, '1 house' addr, null col1, 'Kingston' city, ...
            from dual
          union all
          select '102', '2 egg rd', 'vail', 'guildford', 'Surrey', 'GU1 1LL', '05/dec/1985', 'm', 01452987456
            from dual
          union all
          select '103', '6 station rd', Null, 'guildford', 'Surrey', 'GU1 2XX', '15/may/1990', 'f', 01452987456
            from dual)

I would also caution you to use proper data types and to specify the column names in your INSERT statement. I'm guessing, for example, that the first column of Patient table is some sort of PatientID that is defined as a NUMBER. If so, you'd really want to insert a number rather than a character string. Similarly, the seventh column with values like '15/may/1990' is probably defined as a DATE in the table. If so, your INSERT should insert a DATE not a character string by either explicitly calling TO_DATE with a particular format mask or by using the ANSI date format, i.e. date '1980-01-10'. And if you want the last column to retain the leading 0, you'll need to ensure that the column in the database is defined as a VARCHAR2 and that you insert a character string rather than a number.

Upvotes: 6

Shannon Severance
Shannon Severance

Reputation: 18410

Interesting. It is possible to use insert all to insert multiple rows as you are attempting. Not that I would recommend doing so over Justin's suggested solutions.

The syntax diagram for multi-table insert indicates that a subquery is always required. However, you don't have to use any of the results of the subquery:

SQL> drop table t;

Table dropped.

SQL> create table t (c1 number, c2 varchar2(10));

Table created.

SQL> insert all into t (c1, c2) values (1, 'one')
  2      into t (c1, c2) values (2, 'two')
  3  select * from dual;

2 rows created.

SQL> select * from t;

        C1 C2
---------- ----------
         1 one
         2 two

The two into ... values(...) will cause two rows to be inserted per row in the the subquery:

SQL> insert all into t (c1, c2) values (1, 'one')
  2      into t (c1, c2) values (2, 'two')
  3  select * from dual
  4  connect by level <= 10;

20 rows created.

Upvotes: 1

Related Questions