ALTER PROCEDURE CreateAndPopulate_MSDN_AllData AS

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSDN_Alldata]') AND type in (N'U'))
DROP TABLE [dbo].[MSDN_Alldata]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

select dt.* into MSDN_Alldata from (
select distinct d.* from (
select MSDN.Email, H.* from MSDN_Email MSDN
left join HR_NSV_PM H on H.Email_ID = MSDN.Email
) d
where nullif(d.emplid,'') is not null
union all
select 'MSDN email NOT Found - Name Match' as Email, h.* from (
select distinct MSDN.Email, MSDN.First_Name FN, MSDN.Last_Name LN, H.* from MSDN_Email MSDN
left join HR_NSV_PM H on H.Email_ID = MSDN.Email
where nullif(H.EMPLID,'') is null
) d
left join HR_NSV_PM h on h.LAST_NAME = d.LN and h.FIRST_NAME = d.FN
WHERE NULLIF(h.EMPLID,'') IS NOT NULL
) dt
order by CASE WHEN dt.Email Like 'MSDN%' THEN 1 ELSE 0 END, CASE WHEN nullif(dt.EMPLID,'') IS NULL THEN 1 ELSE 0 END, dt.LAST_NAME, dt.FIRST_NAME

CREATE INDEX idx_MSDN_AllData_EMPLID
ON dbo.MSDN_AllData
(
	EMPLID
)
GO

CREATE INDEX idx_MSDN_AllData_EMAIL
ON dbo.MSDN_AllData
(
	EMAIL
)
GO

CREATE INDEX idx_MSDN_AllData_LAST_NAME
ON dbo.MSDN_AllData
(
	LAST_NAME
)
GO

CREATE INDEX idx_MSDN_AllData_FIRST_NAME
ON dbo.MSDN_AllData
(
	FIRST_NAME
)
GO

CREATE INDEX idx_MSDN_AllData_AU
ON dbo.MSDN_AllData
(
	AU
)
GO

CREATE INDEX idx_MSDN_AllData_AU_NAME
ON dbo.MSDN_AllData
(
	AU_NAME
)
GO

USE [SoftwareReporting]
GO

/****** Object:  StoredProcedure [dbo].[CreateAndPopulate_MSDN_AllData]    Script Date: 06/03/2010 15:21:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreateAndPopulate_MSDN_AllData]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' -- Select into new table MSDN_AllData
 -- All MSDN rows, All columns - includes Email matches and Name matches
 -- name matches have duplicates as there are more than 1 name to match an EMPLID in HR
CREATE PROCEDURE [dbo].[CreateAndPopulate_MSDN_AllData] AS

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[MSDN_Alldata]'') AND type in (N''U''))
DROP TABLE [dbo].[MSDN_Alldata]
' 
END
GO