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.
Related articles :
Difference between Primary Key and Unique Clustered Index in SQL Server- Multiple Markers View in Google Map Integration :
- What is the difference between a Clustered and Non Clustered Index?
- sql server Join Query
- Advance Sql Query
- Security token Key Generagor in C#
Export multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type
Is there any rule to decide what columns should be put in Included in non clustered index and in which order