SELECT Servername, ApplicationID, 'Table1'  AS Appserver FROM Table1 UNION ALL
SELECT Servername, ApplicationID, 'Table2'  AS Appserver FROM Table2 UNION ALL
SELECT Servername, ApplicationID, 'Combine' AS Appserver FROM Combine 

--or is it like this?

SELECT Servername, ApplicationID, CASE 
                                    WHEN Table1.SomeValue IS NULL
                                    Then 'Table2'
                                    WHEN Table2.SomeValue IS NULL
                                    Then 'Table1'
                                    ELSE 'Combine'
                                  END  AS Appserver 
FROM CentralTable

Select IsNull(t1.ServerName,t2.ServerName) as ServerName,
	IsNull(t1.ApplicationName,t2.ApplicationName) as ApplicationName,
	Case 
		When t1.ApplicationName is null then 'Table2'
		When t2.ApplicationName is null then 'Table1'
		Else 'Combine' 
	End AppServer
From Table1 t1
Full Outer Join Table2 t2 on 
	(t1.ServerName = t2.ServerName) and (t1.ApplicationName = t2.ApplicationName)

-- formatted original
select distinct		
	t1.ServerName, 
	t1.[Application ID], 
	t1.[Application Name], 
	t2.VP1, 
	T2.VP2, 
	T2.SVP, 
	T2.[DC2015 Move Group], 
	T1.[System Type], 
	T3.Line,
	
	-- defines 1 column 
	AppServerSource = (
		Select -- returns 3 columns 
			IsNull(t1.ServerName,t2.ServerName) as ServerName,
			IsNull(t1.[Application Name],t2.[Application Name]) as [Application Name],
			Case 
			When t1.[Application Name] is null then 'Check'
			When t2.[Application Name] is null then 'Payorder'
			Else 'Both' 
			End AppServer)
from Table1 t1 
INNER JOIN Table2 t2 
	on t1.[Application ID]= t2.[Application ID] 
	and t1.Servername = t2.ServerName 
JOIN BIM T3 
	ON t1.[Application Name]= t3.[Application Name]
order by Servername -- this is ambiguous and will error - use a table alias

-- alternative
select distinct		
	t1.ServerName, 
	t1.[Application ID], 
	t1.[Application Name], 
	t2.VP1, 
	T2.VP2, 
	T2.SVP, 
	T2.[DC2015 Move Group], 
	T1.[System Type], 
	T3.Line,
	ServerName = IsNull(t1.ServerName,t2.ServerName),
	[Application Name] = IsNull(t1.[Application Name],t2.[Application Name]),
	AppServer = Case 
			When t1.[Application Name] is null then 'Check'
			When t2.[Application Name] is null then 'Payorder'
			Else 'Both' 
			End 
from Table1 t1 
INNER JOIN Table2 t2 
	on t1.[Application ID]= t2.[Application ID] 
	and t1.Servername = t2.ServerName 
INNER JOIN BIM T3 
	ON t1.[Application Name]= t3.[Application Name]
order by t1.Servername