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='06800' and ville.idville=119076 AND fiche.idtype in(88,8,27,121,36,17,97,123,133,116,131,139,117,130,126,124,141,142,143) 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='06800' AND fiche.idtype in(88,8,27,121,36,17,97,123,133,116,131,139,117,130,126,124,141,142,143) 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='06800' and ville.cp=(SELECT CONVERT(char(6),casasoft.dbo.ville.cp) FROM casasoft.dbo.ville WHERE idville=119076) AND type.idtypecasasoft in(88,8,27,121,36,17,97,123,133,116,131,139,117,130,126,124,141,142,143) AND( fiche.ref NOT LIKE 'TIO%') AND( fiche.ref NOT LIKE 'RCO%') order by ref asc;