Advance Sql Query
ALTER procedure [dbo].[usp_generateCitySampleMappingslist]
as
begin
drop TABLE [dbo].[tab_CitySampleMappings_new]
end
begin
CREATE TABLE [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new](
mapid int IDENTITY(1,1),
id int,
mappedto int,
cityid int,
MAPCHECKSTATUS varchar(50),
checkbyuser varchar(50),
MAPCHECKTIME varchar(200)
)
end
begin
insert into tab_CitySampleMappings_new(id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME)
select id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME from IbeHotels.dbo.tab_CitySampleMappings_20150612
where (MAPCHECKSTATUS in ('match') and checkbyuser in ('manish@amantravelgroup.com','swadha@amantravelgroup.com','ravi.sinha@amantravelgroup.com'))
or id=mappedto
union
select id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME from IbeHotels.dbo.tab_CitySampleMappings_20150627
where (MAPCHECKSTATUS in ('match') and checkbyuser in ('manish@amantravelgroup.com','swadha@amantravelgroup.com','ravi.sinha@amantravelgroup.com'))
or id=mappedto
union
select id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME from IbeHotels.dbo.tab_CitySampleMappings
where (MAPCHECKSTATUS in ('match') and checkbyuser in ('manish@amantravelgroup.com','swadha@amantravelgroup.com','ravi.sinha@amantravelgroup.com'))
or id=mappedto
end
--update mappedto
begin
update [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new]
set mappedto=y.id
--select *
from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] X
inner join
(select a.* from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] A
inner join IbeHotels.dbo.tab_hotels B on a.id=b.id
where b.source='hotelbeds') Y on x.mappedto=y.mappedto
--order by y.mappedto
end
begin
update [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new]
set mappedto=y.id
--select *
from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] X
inner join
(select a.* from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] A
inner join IbeHotels.dbo.tab_hotels B on a.id=b.id
where b.source='hpro') Y on x.mappedto=y.mappedto
--order by y.mappedto
end
begin
update [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new]
set mappedto=y.id
--select *
from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] X
inner join
(select a.* from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] A
inner join IbeHotels.dbo.tab_hotels B on a.id=b.id
where b.source='gta') Y on x.mappedto=y.mappedto
--order by y.mappedto
end
begin
update [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new]
set mappedto=y.id
--select *
from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] X
inner join
(select a.* from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] A
inner join IbeHotels.dbo.tab_hotels B on a.id=b.id
where b.source='expedia') Y on x.mappedto=y.mappedto
--order by y.mappedto
end
Thank you for reading the article about Advance Sql Query on the blog NEW TECH If you want to disseminate this article on please list the link as the source, and if this article was helpful please bookmark this page in your web browser by pressing Ctrl + D on your keyboard keys.