select distinct fiche.idagence as idagence,fiche.idfiche,ref,type.nom as nomtype,surface,nbpieces,nbchambresexistantes,isnull(photo.nom_photo,'') as photo,isnull(prixvisible,0) as prixvisible,isnull(prixvisibletxt,0) as prixvisibletxt,ville.nom as nomville,ville.cp as cpville,ville.idville as idville ,isnull(fiche.iscommercial,0) as iscommercial,'1' as typefiche, fiche.creationdate into #tempcasaland from fiche inner join type on type.idtype=fiche.idtype and type.idsupport=fiche.idsupport inner join sup_habitation on sup_habitation.idfiche=fiche.idfiche left join photo on photo.idfiche=fiche.idfiche and typeformat=3 and num_photo=(select top 1 num_photo from photo where photo.idfiche=fiche.idfiche order by photo.ordre asc) left join photo photo1 on photo1.idfiche=fiche.idfiche and photo1.typeformat=3 and photo1.num_photo=(select top 1 num_photo from photo photo2 where photo2.idfiche=photo1.idfiche order by photo2.ordre asc) inner join fichecharge on fichecharge.idfiche=fiche.idfiche left join ficheadresse on ficheadresse.idfiche=fiche.idfiche inner join adresse on adresse.idadresse=ficheadresse.idadresse inner join ville on ville.idville=adresse.idville where idagence not in (6,7,4,12,13,14,114) and isnull(archive,0)=0 and isnull(isinternet,0)=1 AND fiche.idsupport=1 AND left(ville.cp,2)='06' and ville.cp='06500' and ville.idville=118540 AND fiche.idtype in (105,44,25,135,102,22,41,12,31,92,93,32,13,136,140,15,34,95,129,90,10,29,127,128,37,18,98,122,91,30,11) AND fiche.ref not like '%MZI%' AND( fiche.ref NOT LIKE 'TIO%') AND( fiche.ref NOT LIKE 'RCO%') order by ref asc; insert into #tempcasaland select distinct fiche.idagence as idagence,fiche.idfiche,ref,type.nom as nomtype,surface,nbpieces,nbchambresexistantes,isnull(photo.nom_photo,'') as photo,isnull(prixvisible,0) as prixvisible,isnull(prixvisibletxt,0) as prixvisibletxt,ville.nom as nomville,ville.cp as cpville,ville.idville as idville ,isnull(fiche.iscommercial,0) as iscommercial,'3' as typefiche, fiche.creationdate from optimal.dbo.fiche left join optimal.dbo.type on type.idtype=fiche.idtype and type.idsupport=fiche.idsupport left join optimal.dbo.sup_habitation on sup_habitation.idfiche=fiche.idfiche left join optimal.dbo.photo on photo.idfiche=fiche.idfiche and typeformat=3 and num_photo=(select top 1 num_photo from optimal.dbo.photo where photo.idfiche=fiche.idfiche order by photo.ordre asc) left join optimal.dbo.photo photo1 on photo1.idfiche=fiche.idfiche and photo1.typeformat=3 and photo1.num_photo=(select top 1 num_photo from optimal.dbo.photo photo2 where photo2.idfiche=photo1.idfiche order by photo2.ordre asc) left join optimal.dbo.fichecharge on fichecharge.idfiche=fiche.idfiche left join optimal.dbo.ficheadresse on ficheadresse.idfiche=fiche.idfiche left join optimal.dbo.adresse on adresse.idadresse=ficheadresse.idadresse left join optimal.dbo.ville on ville.idville=adresse.idville where isnull(archive,0)=0 and isnull(isinternet,0)=1 AND fiche.idsupport=1 AND left(ville.cp,2)='06' and ville.cp='06500' AND fiche.idtype in (105,44,25,135,102,22,41,12,31,92,93,32,13,136,140,15,34,95,129,90,10,29,127,128,37,18,98,122,91,30,11) AND( fiche.ref NOT LIKE 'TIO%') AND( fiche.ref NOT LIKE 'RCO%') order by ref asc; insert into #tempcasaland select distinct fiche.idagence as idagence,fiche.idfiche,ref,type.nom as nomtype,surface,nbpieces,nbchambresexistantes,isnull(photo.nom_photo,'') as photo,isnull(prixvisible,0) as prixvisible,isnull(prixvisibletxt,0) as prixvisibletxt,ville.nom as nomville,ville.cp as cpville,ville.idville as idville ,isnull(fiche.iscommercial,0) as iscommercial,'2' as typefiche, fiche.creationdate from zingrafsoft.dbo.fiche inner join zingrafsoft.dbo.type on type.idtype=fiche.idtype and type.idsupport=fiche.idsupport inner join zingrafsoft.dbo.sup_habitation on sup_habitation.idfiche=fiche.idfiche left join zingrafsoft.dbo.photo on photo.idfiche=fiche.idfiche and typeformat=3 and num_photo=(select top 1 num_photo from zingrafsoft.dbo.photo where photo.idfiche=fiche.idfiche order by isnull(photo.ordre,100) asc) left join zingrafsoft.dbo.photo photo1 on photo1.idfiche=fiche.idfiche and photo1.typeformat=3 and photo1.num_photo=(select top 1 num_photo from photo photo2 where photo2.idfiche=photo1.idfiche order by isnull(photo.ordre,100) asc) inner join zingrafsoft.dbo.fichecharge on fichecharge.idfiche=fiche.idfiche left join zingrafsoft.dbo.ficheadresse on ficheadresse.idfiche=fiche.idfiche inner join zingrafsoft.dbo.adresse on adresse.idadresse=ficheadresse.idadresse inner join zingrafsoft.dbo.ville on ville.idville=adresse.idville where isnull(archive,0)=0 and isnull(isinternet,0)=1 AND fiche.idsupport=1 AND left(ville.cp,2)='06' and ville.cp='06500' and ville.cp=(SELECT CONVERT(char(6),casasoft.dbo.ville.cp) FROM casasoft.dbo.ville WHERE idville=118540) AND type.idtypecasasoft in (105,44,25,135,102,22,41,12,31,92,93,32,13,136,140,15,34,95,129,90,10,29,127,128,37,18,98,122,91,30,11) AND( fiche.ref NOT LIKE 'TIO%') AND( fiche.ref NOT LIKE 'RCO%') order by ref asc;