Insert w/ multiple conditions... 1

[dakone]

It must be Monday because I can't get anything to work today.

INSERT  INTO aSph_IMSworkEquip 
   SELECT  NEW.NO_SERIE, NEW.NO_CLIENT, NEW.NO_PRODUIT, NEW.VIGUEUR, NEW.EXPIRE, NEW.NO_CONTRAT, NEW.ADR_EQUIP
     FROM aSph_IMSrawEquipImport AS NEW,RM00101,RM00102
    WHERE NEW.NO_CONTRAT <> 0 OR NEW.NO_CONTRAT IS NOT NULL 
      AND NEW.NO_CLIENT = RM00101.CUSTNMBR
      AND NEW.ADR_EQUIP = RM00102.ADDRESS1

This times out in Query Analyzer and I'm having trouble figuring out why. Anybody have any ideas?

 

[swampBoogie]

I don't understand the purpose of having both of these conditions

WHERE NEW.NO_CONTRAT <> 0 OR NEW.NO_CONTRAT IS NOT NULL

Anyhow, you should use

WHERE ( NEW.NO_CONTRAT <> 0 OR NEW.NO_CONTRAT IS NOT NULL )

How many rows are there in the tables?

 

[SQLSister]

Check your execution plan, it should tell you if it using indexes or table scans. Maybe you need better indexing.

Another possibility is that it is interpreting this as a cross join. That often results in a timeout problem because it creates a huge recordset. I prefer never to use this style of coding anyway. Better to be explicit with your joins than mix them in the where clause. Try the following code. If it does not time out, then your code was producing a cross join. I tried your methodolgy with some of my tables and also timed out where my query ran in less than a second. This supports my idea that you may be inadvvertently creating a cross join.

INSERT INTO aSph_IMSworkEquip
SELECT NEW.NO_SERIE, NEW.NO_CLIENT, NEW.NO_PRODUIT, NEW.VIGUEUR, NEW.EXPIRE, NEW.NO_CONTRAT, NEW.ADR_EQUIP
FROM aSph_IMSrawEquipImport AS NEW
INNER JOIN RM00101 ON NEW.NO_CLIENT = RM00101.CUSTNMBR INNER JOIN RM00102 ON NEW.ADR_EQUIP = RM00102.ADDRESS1
WHERE NEW.NO_CONTRAT <> 0 OR NEW.NO_CONTRAT IS NOT NULL

 

[pascalsql]

comment out the insert line
and replace the selected columns with count(*)
That will indicate how many rows you are about to insert.

SELECT COUNT(*)
FROM aSph_IMSrawEquipImport AS NEW,RM00101,RM00102
WHERE NEW.NO_CONTRAT <> 0 OR NEW.NO_CONTRAT IS NOT NULL
AND NEW.NO_CLIENT = RM00101.CUSTNMBR
AND NEW.ADR_EQUIP = RM00102.ADDRESS1

Maybe a quintillion new contracts exceeds the best expectations of the sales dept!

There is no restriction on RM00101 and RM00102. It most probably ends up with a cross-joins, ie #rows of RM00101 * #rows of RM00102.