select TOP 1 fiche.idagence as idagence,fiche.idfiche,descriptionweb.texte as descriptionweb,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 #temp 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 inner join descriptionweb on descriptionweb.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(fiche.isinternet,0)=1 and idlangue in (select idlangue from langue where nom like 'fra%') and photo.nom_photo<>'' AND( fiche.ref NOT LIKE 'TIO%') AND( fiche.ref NOT LIKE 'RCO%') and fiche.creationdate between dateadd(dd,-14,getdate()) and getdate() order by newid()

insert into #temp select TOP 1 fiche.idagence as idagence,fiche.idfiche,descriptionweb.texte as descriptionweb,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 left join zingrafsoft.dbo.type on type.idtype=fiche.idtype and type.idsupport=fiche.idsupport left join zingrafsoft.dbo.sup_habitation on sup_habitation.idfiche=fiche.idfiche left join zingrafsoft.dbo.descriptionweb on descriptionweb.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 photo.ordre 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 zingrafsoft.dbo.photo photo2 where photo2.idfiche=photo1.idfiche order by photo2.ordre asc) left join zingrafsoft.dbo.fichecharge on fichecharge.idfiche=fiche.idfiche left join zingrafsoft.dbo.ficheadresse on ficheadresse.idfiche=fiche.idfiche left join zingrafsoft.dbo.adresse on adresse.idadresse=ficheadresse.idadresse left join zingrafsoft.dbo.ville on ville.idville=adresse.idville where isnull(archive,0)=0 and isnull(fiche.isinternet,0)=1 and idlangue in (select idlangue from zingrafsoft.dbo.langue where nom like 'fra%') and photo.nom_photo<>'' AND( fiche.ref NOT LIKE 'TIO%') AND( fiche.ref NOT LIKE 'RCO%') and fiche.creationdate between dateadd(dd,-14,getdate()) and getdate() order by newid()

insert into #temp select TOP 1 fiche.idagence as idagence,fiche.idfiche,descriptionweb.texte as descriptionweb,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.descriptionweb on descriptionweb.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(fiche.isinternet,0)=1 and idlangue in (select idlangue from optimal.dbo.langue where nom like 'fra%') and photo.nom_photo<>'' AND( fiche.ref NOT LIKE 'TIO%') AND( fiche.ref NOT LIKE 'RCO%') and fiche.creationdate between dateadd(dd,-14,getdate()) and getdate() order by newid()

select TOP 1 fiche.idagence as idagence,fiche.idfiche,descriptionweb.texte as descriptionweb,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 #temp 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 inner join descriptionweb on descriptionweb.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(fiche.isinternet,0)=1 and idlangue in (select idlangue from langue where nom like 'fra%') and photo.nom_photo<>'' AND( fiche.ref NOT LIKE 'TIO%') AND( fiche.ref NOT LIKE 'RCO%') and fiche.creationdate between dateadd(dd,-14,getdate()) and getdate() order by newid() ; insert into #temp select TOP 1 fiche.idagence as idagence,fiche.idfiche,descriptionweb.texte as descriptionweb,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.descriptionweb on descriptionweb.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(fiche.isinternet,0)=1 and idlangue in (select idlangue from optimal.dbo.langue where nom like 'fra%') and photo.nom_photo<>'' AND( fiche.ref NOT LIKE 'TIO%') AND( fiche.ref NOT LIKE 'RCO%') and fiche.creationdate between dateadd(dd,-14,getdate()) and getdate() order by newid() ; insert into #temp select TOP 1 fiche.idagence as idagence,fiche.idfiche,descriptionweb.texte as descriptionweb,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 left join zingrafsoft.dbo.type on type.idtype=fiche.idtype and type.idsupport=fiche.idsupport left join zingrafsoft.dbo.sup_habitation on sup_habitation.idfiche=fiche.idfiche left join zingrafsoft.dbo.descriptionweb on descriptionweb.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 photo.ordre 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 zingrafsoft.dbo.photo photo2 where photo2.idfiche=photo1.idfiche order by photo2.ordre asc) left join zingrafsoft.dbo.fichecharge on fichecharge.idfiche=fiche.idfiche left join zingrafsoft.dbo.ficheadresse on ficheadresse.idfiche=fiche.idfiche left join zingrafsoft.dbo.adresse on adresse.idadresse=ficheadresse.idadresse left join zingrafsoft.dbo.ville on ville.idville=adresse.idville where isnull(archive,0)=0 and isnull(fiche.isinternet,0)=1 and idlangue in (select idlangue from zingrafsoft.dbo.langue where nom like 'fra%') and photo.nom_photo<>'' AND( fiche.ref NOT LIKE 'TIO%') AND( fiche.ref NOT LIKE 'RCO%') and fiche.creationdate between dateadd(dd,-14,getdate()) and getdate() order by newid() ;