Hi,

I search in the documentation and could not find how to insert multiple rows using a single SQL Statment. According to SQL-92 it should be something like (from http://en.wikipedia.org/wiki/Insert_(SQL) ):


INSERT INTO table (column1, [column2, ... ])
VALUES (value1a, [value1b, ...]),
(value2a, [value2b, ...]),
...
Unfortunatelly it did not work in firebird. Do we have a way to insert multiple rows in a single statment in firebird?

I want to reduce time by inserting everything i need in a single statment.

I wanna insert several currency quotes in tbcurrencyquotes in the following format:

USD EUR 2008 0.93
BRL AUD 2007 x.xxx

USD EUR 2009 0.98
BRL AUD 2009 x.xxx

Here is the tables i have:

//currencies table
CREATE TABLE TBCURRENCY (CURRENCYID INT, CURRENCYCODE VARCHAR(3) NOT NULL, CURRENCYNAME VARCHAR(50) NOT NULL, PRIMARY KEY(CURRENCYCODE));
ALTER TABLE TBCURRENCY ADD CHECK(CURRENCYCODE <> '');
ALTER TABLE TBCURRENCY ADD CHECK(CURRENCYNAME <> '');
ALTER TABLE TBCURRENCY ADD CONSTRAINT CON_CURRENCYID_UNIQUE UNIQUE(CURRENCYID);

//store the years for each currency exchange table
CREATE TABLE TBCURRENCYQUOTEYEAR (CURRENCYQUOTEYEAR INTEGER NOT NULL, PRIMARY KEY(CURRENCYQUOTEYEAR));
ALTER TABLE TBCURRENCYQUOTEYEAR ADD CHECK(CURRENCYQUOTEYEAR <> 0);


//store the exchange quotes for the currencies
CREATE TABLE TBCURRENCYQUOTES (BASECURRENCY VARCHAR(3) NOT NULL, QUOTECURRENCY VARCHAR(3) NOT NULL, CURRENCYQUOTEYEAR INTEGER, PRICE FLOAT DEFAULT 1, PRIMARY KEY(BASECURRENCY,QUOTECURRENCY,CURRENCYQUOTEYEAR), FOREIGN KEY(BASECURRENCY) REFERENCES TBCURRENCY(CURRENCYCODE) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(QUOTECURRENCY) REFERENCES TBCURRENCY(CURRENCYCODE) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(CURRENCYQUOTEYEAR) REFERENCES TBCURRENCYQUOTEYEAR(CURRENCYQUOTEYEAR) ON DELETE CASCADE ON UPDATE CASCADE);
ALTER TABLE TBCURRENCYQUOTE ADD CHECK(BASECURRENCY <> '');
ALTER TABLE TBCURRENCYQUOTE ADD CHECK(QUOTECURRENCY <> '');



//generator to automatic assign a code for the currency
CREATE GENERATOR gen_CURRENCY_ID;
SET GENERATOR gen_CURRENCY_ID TO 0;

SET TERM !! ;
CREATE TRIGGER TBCURRENCY_BI FOR TBCURRENCY
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.CURRENCYID IS NULL) THEN
NEW..CURRENCYID = GEN_ID(GEN_CURRENCY_ID, 1);
END!!
SET TERM ; !!

---
Luís Carlos Dill Junges ©

"A realidade de cada lugar e de cada época é uma alucinação coletiva."
Bloom, Howard


Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua cara @... ou @....
http://br.new.mail.yahoo.com/addresses

[Non-text portions of this message have been removed]