Insert values 2 ways- 0 rows inserted-CodePudding

Trying to add 7 rows in 2 different ways. In both the result is 0 rows inserted.

create table bank
(
  MoveNo INT ,
  Valdate DATE not null,
  Ammount NUMBER(9,2),
  Essence varchar2(30)
);

**//First way**
INSERT ALL
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (1,'2001/01/01',500,'opening balance')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (2,'2001/01/10',3500,'salary')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (3,'2001/01/10',-460,'cheque 055786')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (4,'2001/01/12',1100,'deposit')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (5,'2001/01/14',-5.50,'account managment')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (6,'2001/01/17',-300,'cheque 055787')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (7,'2001/01/19',-867,'credit card')
SELECT 7 FROM BANK;

**//Second way**
INSERT INTO bank (MoveNo, Valdate, Ammount, Essence)
          select 1,'2001/01/01',500,'opening balance' from dual
union all select 2,'2001/01/10',3500,'salary' from dual
union all select 3,'2001/01/10',-460,'cheque 055786' from dual,
union all select 4,'2001/01/12',1100,'deposit' from dual,
union all select 5,'2001/01/14',-5.50,'account managment' from dual,
union all select 6,'2001/01/17',-300,'cheque 055787' from dual,
union all select 7,'2001/01/19',-867,'credit card' from dual,
SELECT * FROM bank;

CodePudding user response：

For both statements, '2001/01/01' is not a DATE it is a string literal. Although Oracle will try to be helpful and implicitly apply the TO_DATE function to the string to convert the string to a date, it does not know the format of the date so it will use the NLS_DATE_FORMAT. You are likely to get an error as the default NLS_DATE_FORMAT is not likely to match YYYY/MM/DD. Instead, you should use a DATE literal or use TO_DATE('2001/01/01', 'YYYY-MM-DD').

The BANK table has zero rows initially, so when you use INSERT ALL ... SELECT 7 FROM BANK the final SELECT will match zero rows and nothing will be inserted. Instead, you want to SELECT ... FROM DUAL which would give you a single row and correspondingly perform each of the INSERTs once:

INSERT ALL
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (1, DATE '2001-01-01',  500,    'opening balance')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (2, DATE '2001-01-10', 3500,    'salary')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (3, DATE '2001-01-10', -460,    'cheque 055786')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (4, DATE '2001-01-12', 1100,    'deposit')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (5, DATE '2001-01-14',   -5.50, 'account managment')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (6, DATE '2001-01-17', -300,    'cheque 055787')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (7, DATE '2001-01-19', -867,    'credit card')
SELECT 1 FROM DUAL;

For your second query, you have commas between the statements and are missing the final ; statement terminator:

INSERT INTO bank (MoveNo, Valdate, Ammount, Essence)
          select 1, DATE '2001-01-01',  500,    'opening balance'   from dual
union all select 2, DATE '2001-01-10', 3500,    'salary'            from dual
union all select 3, DATE '2001-01-10', -460,    'cheque 055786'     from dual
union all select 4, DATE '2001-01-12', 1100,    'deposit'           from dual
union all select 5, DATE '2001-01-14',   -5.50, 'account managment' from dual
union all select 6, DATE '2001-01-17', -300,    'cheque 055787'     from dual
union all select 7, DATE '2001-01-19', -867,    'credit card'       from dual;

Then you can do:

SELECT * FROM BANK;

As a separate statement and it will show 14 rows (7 from the INSERT ALL statement and 7 from the INSERT .. SELECT statement).

db<>fiddle here