Re: Inserting rows into two joined tables using 'sqlca.sqlerrd1'

citynomad wrote: > Hi folks, > > I am trying to insert rows into two joined tables using > dbinfo('sqlca.sqlerrd1') in a loop.> > Here is the SQL I'm using: > > CREATE PROCEDURE insert_offers > DEFINE deptcd, upsellcd CHAR(3);> > FOREACH SELECT department_cd, upsell_cd INTO deptcd, upsellcd FROM > mkt_backup > insert into marketing_text (upsell_cd) > values (upsellcd) > insert into department_text (department_cd, marketing_text_id) > values (deptcd, dbinfo('sqlca.sqlerrd1') > END FOREACH > END PROCEDURE> > Is this the right way to do it? Does it work? If so, it is a reasonable way to do it, though I'd feel more comfortable if you collected the value of sqlca.sqlerrd1 before executing the second statement - you might get confusing results. > Is there an easier way to do this in > informix - perhaps without a stored procedure? INSERT INTO Marketing_Text(Upsell_cd) SELECT Upsell_cd FROM Mkt_Backup; INSERT INTO Department_Text(Department_cd, Marketing_Text_ID) SELECT B.Department_cd, T.SerialColumnWhoseNameYouDidNotMention FROM Mkt_backup B, Marketing_text T WHERE B.Upsell_cd = T.Upsell_cd; There are many unanswerable questions arising - since you haven't given us sufficient schema information or details of the constraints. For example, is the upsell code and department code unique in the marketing backup table? Is the upsell code in the marketing text unique? What are you really trying to do? The two statement version of the code, if it can be made to work, should be quicker than the loop version, even in a stored procedure, provided that there are indexes in the appropriate places. > (I am a java programmer and never used Informix until recently, so bear > with me.) This is mostly a question of thinking in SQL terms - though DBINFO('sqlca.sqlerrd1') is very Informix-specific, the concept appliesto all auto-incrementing columns. Did you consider using a sequence instead of a serial column? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/