more than one insert statement

[TASC]

Hi,

is there a way you can insert into an oracle table more than one row through the FileMaker Execute Sql script step?

my sql is as follows:

insert into emp(empno,ename,job) values('1234','Constance','guru')

/

Insert into dept(deptno,dname,loc) values('12','Accounting','Calgary')

/

I keep getting an error that either that there's an invalid character (when I use the ";")or that the Sql command not properly ended (when I use the / to represent a new sql insert command)

Thanks for your help in advance!

Constance

[The Shadow]

Sql-92 allows a single insert statement to contain multiple rows, I believe it works like this:

insert into emp (empno,ename,job) values

    ('1234','Constance' ,'guru'), ('12','Accounting', 'Calgary')

There's a rough-draft of the SQL-92 standard at: SQL-92 standard

See page 176 for a discussion of this (called table value constructor's).

Reading the page a little myself, it appears that SQL engine's that only implement the "Intermediate" level don't have to support more than one row. But I would expect Oracle to implement the full standard.

Ooops, just re-read your original post, and I see you were referring to different tables. I don't know of any way to do that.

Edited November 24, 2007 by Guest

[TimD]

I think you'd need to use a stored procedure to do this. The stored procedure would take the values that you are inserting as parameters, then do the two inserts.

-- Tim

[TASC]

Do you have an example of a stored procedure that would do this?

I would greatly appreciate it.

Constance

[TimD]

Constance --

No, I don't. I do most of my SQL development on SQL Server, not on Oracle. But your Oracle DBA should be able to whip up a stored procedure to do this fairly easily.

-- Tim