Create View For All Table Names Listed In Table I know this data structure is morbidly incorrect, but I am playing with the cards I have been dealt as I can not go back and re-write the structure.  I have a table that holds table names, I need to iterate each table name and create a view with the data from each of those table names.  Like below is what I need to do, BUT my issue is that I iterate each table name and the view is dropped, not appended.

Declare @employee varchar(100), @sql varchar(4000), @location varchar(300)

select RTRIM(LTRIM(name)) As employee
INTO #TempTable1
from alphawhiskeycharlie
where name LIKE 'AL%'
AND (terminationdate IS NULL
OR CAST(terminationdate As Date) &gt;= GetDate()-90)
ORDER BY RTRIM(LTRIM(name)) ASC

Declare c1 Cursor For

Select employee
FROM #TempTable1

Open c1 

Fetch Next From c1 Into @employee

Set @location = (Select fulltablelocation from servername.databasename.dbo.tableinformation where employeename = @employee)

While @@FETCH_STATUS = 0
  Begin
    Set @sql = 'IF OBJECT_ID('TestView', 'V') IS NOT NULL '
                + 'DROP VIEW TestView '
                + 'Create View [TestView] As '
                +'Select '''+@employee+''' As ''employee'', '
                +'COUNT(employeeID) As [TotalEmployees] '
                +'From '+@location+' '
                +'where employeename is not null'
    Print(@sql)
    exec(@sql)

    Fetch Next From c1 Into @employee

End

Close c1
Untested... but something like this: 

Declare @employee varchar(100), @sql varchar(4000), @location varchar(300)

select RTRIM(LTRIM(name)) As employee
INTO #TempTable1
from alphawhiskeycharlie
where name LIKE 'AL%'
AND (terminationdate IS NULL
OR CAST(terminationdate As Date) &gt;= GetDate()-90)
ORDER BY RTRIM(LTRIM(name)) ASC

Declare c1 Cursor For

Select employee
FROM #TempTable1

Open c1 

Fetch Next From c1 Into @employee

Set @location = (Select fulltablelocation from servername.databasename.dbo.tableinformation where employeename = @employee)

Set @sql = 'IF OBJECT_ID('TestView', 'V') IS NOT NULL '
                + 'DROP VIEW TestView '
                + 'Create View [TestView] As '
While @@FETCH_STATUS = 0
  Begin
    Set @sql = @sql 
                +'Select '''+@employee+''' As ''employee'', '
                +'COUNT(employeeID) As [TotalEmployees] '
                +'From '+@location+' '
                +'where employeename is not null

                UNION ' 


    Fetch Next From c1 Into @employee

End

-- remove the last UNION
Set @sql = Substring(@sql,1,len(@sql)-10)

Print(@sql)    
exec(@sql)