insert...returning / select

Hi

I have a JSP/JDBC program that processes sql statements from other JSPs to an Oracle 8.16 database. I need to modify this program to retreive the data that was just inserted (from different tables, with different keys and different INSERT statement structures).

The best for me would be to perform a SELECT *... for the inserted record... but I just can't figure out how to retreive this record and it's becoming frustrating !!

I've been searching for a way to do this with PL/SQL 'INSERT... RETURNING...' but everything I found on the web isn't clear and i'm quite new to SQL and JDBC.

Could someone PLEASE clearly explain to me if it's possible and HOW... if not, is there any way I can ever achieve this without having to tear down the INSERT statement and build some sort of a SELECT statement out of it ??Hi,

the syntax is

insert into table (column1,column2,column3)
values(1,2,3)
returning column1,column2 into variable1,variable2

but it doesn't work with multitable-inserts and it is really slowly (on 9i)
the faster way is

select primary_key from sequence.nextval into variable...
then insert with this pk an reselect the values inserted...

good luck|||In general it shouldn't be necessary to SELECT to find out what you just inserted - you know what you just inserted! The only exceptions are values set by DEFAULT clauses or triggers.

It would be a good idea (good practice) to take the insert statements out of the JSP code and put them in PL/SQL packaged procedures. These procedures can then have OUT arguments to return the required data. However, I imagine that is a big change from where you are now.|||I see no problem with the performance of INSERT RETURNING on 9i database. It appears to perform rather better that SELECT then INSERT even WITHOUT further SELECT to retrieve row.

Frankly I'm disappointed when I see people making assertions of this kind without any evidence. There's enough Oracle misconceptions floating around without adding to the steaming pile.

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> CREATE TABLE table_name (column_name NUMBER (10));

Table created.

SQL> CREATE UNIQUE INDEX index_name ON table_name (column_name);

Index created.

SQL> CREATE SEQUENCE sequence_name INCREMENT BY 1 CACHE 10000;

Sequence created.

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 v_start_time INTEGER := 0;
3 v_column_name NUMBER (10);
4 v_sequence_no NUMBER (10);
5 v_iterations INTEGER := 10000;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE ('Case 1: SELECT then INSERT');
8 v_start_time := DBMS_UTILITY.GET_TIME;
9 FOR i IN 1..v_iterations LOOP
10 SELECT sequence_name.NEXTVAL
11 INTO v_sequence_no
12 FROM dual;
13 INSERT INTO table_name (column_name)
14 VALUES (v_sequence_no);
15 END LOOP;
16 DBMS_OUTPUT.PUT_LINE ('Hsecs: ' ||
17 (DBMS_UTILITY.GET_TIME - v_start_time));
18
19 DBMS_OUTPUT.PUT_LINE ('Case 2: INSERT RETURNING');
20 v_start_time := DBMS_UTILITY.GET_TIME;
21 FOR i IN 1..v_iterations LOOP
22 INSERT INTO table_name (column_name)
23 VALUES (sequence_name.NEXTVAL)
24 RETURNING column_name INTO v_column_name;
25 END LOOP;
26 DBMS_OUTPUT.PUT_LINE ('Hsecs: ' ||
27 (DBMS_UTILITY.GET_TIME - v_start_time));
28 END;
29 /
Case 1: SELECT then INSERT
Hsecs: 202
Case 2: INSERT RETURNING
Hsecs: 129

PL/SQL procedure successfully completed.

SQL> /
Case 1: SELECT then INSERT
Hsecs: 196
Case 2: INSERT RETURNING
Hsecs: 153

PL/SQL procedure successfully completed.

SQL> /
Case 1: SELECT then INSERT
Hsecs: 199
Case 2: INSERT RETURNING
Hsecs: 118

PL/SQL procedure successfully completed.

SQL>

Insert, Calculations & Where

Hi

I sometimes find myself in the situation where I want to insert a row into a table using the following form:
insert table ( <field list> ) select <field list> from .. etc .. Where <conditions>

My question is to do with where one or more of the fields in the select field list are calculations and where I also want to use some/all of these derived fields as Where conditions. [ Eg: only insert if the calculated value is > 0]

I currently either repeat the calculation in the Where clause or move it to a function and use the function call in both places. (I always get a pang of guilt using either option - repeating the calculation feels like bad practice - & using the function twice seems inefficient (does this get optimised?)).

I could get a life & stop worrying - but is there a better/neater way of doing this?

Many thanks.An exact DML sample would be helpful here...but if you need to INSERT a derived field, and need to make sure that the derived field is > 0 for example, then you have no choice...|||Use HAVING clause|||Use HAVING clause

Is it 5:00 already in texas?

Insert trigger

Hi

I have a table - DebtorTurnover - consisting of 5 fields (ID, Date,
Turnover, VAT, Netturnover). I get a file which I have to import every
know and then, with new data. In this file I only get values for (ID,
Date, Turnover and VAT). The import is working fine with the import
wizard.

The problem is, that I want to have the Netturnover computed at the
time of insert to equal [Turnover-VAT], but I don't really know how to
as I'm new to these triggers.

Could anyone help me I would appriciate this.
BR / Janjazpar (jannoergaard@.hotmail.com) writes:
> I have a table - DebtorTurnover - consisting of 5 fields (ID, Date,
> Turnover, VAT, Netturnover). I get a file which I have to import every
> know and then, with new data. In this file I only get values for (ID,
> Date, Turnover and VAT). The import is working fine with the import
> wizard.
> The problem is, that I want to have the Netturnover computed at the
> time of insert to equal [Turnover-VAT], but I don't really know how to
> as I'm new to these triggers.

The simplest is to make NetTurnover a computed column:

CREATE TABLE DebtorTurnover
(ID int NOT NULL,
Date datetime NOT NULL,
Turnover decimal(10,2) NOT NULL,
VAT decimal(10, 2) NOT NULL,
Netturnover AS Turnover - VAT)

A trigger would look like this:

CREATE TRIGGER DebtorTurnover_tri ON DebtorTurnover
FOR INSERT, UPDATE AS
UPDATE DebtorTurnover
SET Netturnover = dt.Turnover - dt.VAT
FROM DebtorTurnover dt
WHERE EXISTS (SELECT *
FROM inserted dt
WHERE dt.ID = i.ID

The "inserted" table is a virtual table that holds the inserted rows,
or in case of an UPDATE, the update rows after the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog skrev:

> jazpar (jannoergaard@.hotmail.com) writes:
> > I have a table - DebtorTurnover - consisting of 5 fields (ID, Date,
> > Turnover, VAT, Netturnover). I get a file which I have to import every
> > know and then, with new data. In this file I only get values for (ID,
> > Date, Turnover and VAT). The import is working fine with the import
> > wizard.
> > The problem is, that I want to have the Netturnover computed at the
> > time of insert to equal [Turnover-VAT], but I don't really know how to
> > as I'm new to these triggers.
> The simplest is to make NetTurnover a computed column:
> CREATE TABLE DebtorTurnover
> (ID int NOT NULL,
> Date datetime NOT NULL,
> Turnover decimal(10,2) NOT NULL,
> VAT decimal(10, 2) NOT NULL,
> Netturnover AS Turnover - VAT)
> A trigger would look like this:
> CREATE TRIGGER DebtorTurnover_tri ON DebtorTurnover
> FOR INSERT, UPDATE AS
> UPDATE DebtorTurnover
> SET Netturnover = dt.Turnover - dt.VAT
> FROM DebtorTurnover dt
> WHERE EXISTS (SELECT *
> FROM inserted dt
> WHERE dt.ID = i.ID
> The "inserted" table is a virtual table that holds the inserted rows,
> or in case of an UPDATE, the update rows after the table.
Hi Thanks for you reply

I made the following

Table:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DepTurnOver]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[DepTurnOver]
GO

CREATE TABLE [dbo].[DepTurnOver] (
[Year] [int] NULL ,
[Week] [int] NULL ,
[CalleId] [int] NULL ,
[ShopId] [int] NULL ,
[ItemGroupId] [int] NULL ,
[TurnOver] [real] NULL ,
[Discount] [real] NULL ,
[Qty] [real] NULL ,
[Customer] [int] NULL ,
[VAT] [real] NULL ,
[Consumption] [real] NULL,
[Netturnover] AS [Turnover]-[VAT]
) ON [PRIMARY]
GO

Trigger:
CREATE TRIGGER DepTurnover_tri ON DepTurnover
FOR INSERT, UPDATE AS
UPDATE DepTurnover
SET Netturnover = idt.Turnover - idt.VAT
FROM DepTurnover idt
WHERE EXISTS (SELECT *
FROM inserted dt
WHERE dt.Year = idt.Year
AND dt.Week = idt.week
AND dt.CalleId = idt.CalleId
AND dt.ShopId = idt.ShopId
AND dt.ItemGroupId = idt.ItemGroupId)

But when I try to save the trigger I get the following error:
Server: Msg 271, Level 16, State 1, Procedure DepTurnover_tri, Line 3
Column 'Netturnover' cannot be modified because it is a computed
column.

Have I done anything wrong here.

Thanks in advance
BR/ Jan

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||jazpar (jannoergaard@.hotmail.com) writes:
> I made the following
> Table:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[DepTurnOver]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[DepTurnOver]
> GO
> CREATE TABLE [dbo].[DepTurnOver] (
> [Year] [int] NULL ,
> [Week] [int] NULL ,
> [CalleId] [int] NULL ,
> [ShopId] [int] NULL ,
> [ItemGroupId] [int] NULL ,
> [TurnOver] [real] NULL ,
> [Discount] [real] NULL ,
> [Qty] [real] NULL ,
> [Customer] [int] NULL ,
> [VAT] [real] NULL ,
> [Consumption] [real] NULL,
> [Netturnover] AS [Turnover]-[VAT]
> ) ON [PRIMARY]
> GO
> Trigger:

Sorry, I was a bit brief. If you have a computed column, you don't
need the trigger at all. I included the trigger code, in case you
were not in position to change the table definition.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Insert rows into a Flat File

Hi!

I have a problem that I can′t understand.

I have rows in a table in a SQL Server 2005 that I want to send to a Flat file. When I am executing the task for the first time it is OK and works perfectly. But the problem is when I am trying to do it again. I seems like the path to the file and the filename dissappears after the first time and the execution failes with an error message that tells me that the file is invalid and don′t exists.

I don′t now why this is happening and it′s such an easy thing to do and I can′t make it work.

So please help me!

Regards,

Johanna

I have no idea from what you descripted

I think you should double check your setting or programming

|||

Johanna Maria wrote:

Hi!

I have a problem that I can′t understand.

I have rows in a table in a SQL Server 2005 that I want to send to a Flat file. When I am executing the task for the first time it is OK and works perfectly. But the problem is when I am trying to do it again. I seems like the path to the file and the filename dissappears after the first time and the execution failes with an error message that tells me that the file is invalid and don′t exists.

I don′t now why this is happening and it′s such an easy thing to do and I can′t make it work.

So please help me!

Regards,

Johanna

Can you post the full error message?

Does this happen at design-time or execution-time?

-Jamie