select distinct TOP 20 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=2 AND fiche.ref not like '%MZI%' AND( fiche.ref NOT LIKE 'TIO%') AND( fiche.ref NOT LIKE 'RCO%') order by fiche.creationdate desc; insert into #tempcasaland select distinct TOP 20 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=2 AND( fiche.ref NOT LIKE 'TIO%') AND( fiche.ref NOT LIKE 'RCO%') order by fiche.creationdate desc; insert into #tempcasaland select distinct TOP 20 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=2 AND( fiche.ref NOT LIKE 'TIO%') AND( fiche.ref NOT LIKE 'RCO%') order by fiche.creationdate desc;