Select UNION from multiple tables via variable

Question

I have a query that selects from multiple tables using a join. I want to execute this query from different databases via a loop.

I have accomplished that via (simplified query):

DECLARE @intCounter int
SET @intCounter = 1

DECLARE @tblBedrijven TABLE (ID int identity(1,1), 
                        CompanyName varchar(20),
                        DatabaseTable varchar(100))
INSERT INTO @tblBedrijven VALUES ('001-CureCare', '<TABLE/ DATABASE1> AUS'), 
                            ('002-Cleaning', '[global_nav5_prod].[dbo].<TABLE/ DATABASE2>] AUS')
DECLARE @strCompany varchar(20)
DECLARE @strTable varchar(100)

WHILE (@intCounter <= (SELECT MAX(ID) FROM @tblBedrijven))
BEGIN
    SET @strTable = (SELECT DatabaseTable FROM @tblBedrijven
                WHERE ID = @intCounter)
    SET @strCompany = (SELECT CompanyName FROM @tblBedrijven
                WHERE ID = @intCounter)
    EXEC('SELECT ''' + @strCompany + ''' as Company,
        AUS.[User],
        AUS.[E-mail]
    FROM' + @strTable)
    SET @intCounter = @intCounter + 1
END

My problem is that the result generates 2 separate tables (for every loop). I want to union the results but have no clue how.

Any suggestions?

Thanks in advance.

shazin shazin · Accepted Answer · 2014-01-03T07:57:05

Can't you use something like the below code where you append all the sqls with union and finally execute the sql once only without executing in a loop. I am not an expert in SQL Server but I have written many other similar stored procedures using other RDBMS. So please bear any syntax errors.

DECLARE @intCounter int
DECLARE @maxId int
SET @intCounter = 1    

DECLARE @tblBedrijven TABLE (ID int identity(1,1), 
                        CompanyName varchar(20),
                        DatabaseTable varchar(100))
INSERT INTO @tblBedrijven VALUES ('001-CureCare', '<TABLE/ DATABASE1> AUS'), 
                            ('002-Cleaning', '[global_nav5_prod].[dbo].<TABLE/ DATABASE2>] AUS')
DECLARE @strCompany varchar(20)
DECLARE @strTable varchar(100)
DECLARE @strSql varchar(5000)
SET @maxId = (SELECT MAX(ID) FROM @tblBedrijven)

WHILE (@intCounter <= @maxId)
BEGIN

    SET @strTable = (SELECT DatabaseTable FROM @tblBedrijven
                WHERE ID = @intCounter)
    SET @strCompany = (SELECT CompanyName FROM @tblBedrijven
                WHERE ID = @intCounter)
    SET @strSql = @strSql + ('SELECT ''' + @strCompany + ''' as Company,
        AUS.[User],
        AUS.[E-mail]
    FROM' + @strTable)
    IF @intCounter < @maxId THEN
    BEGIN
        SET @strSql = @strSql + ' UNION '
    END
    SET @intCounter = @intCounter + 1       
END

EXEC(@strSql)

Select UNION from multiple tables via variable

1 Answers