I want to iterate the table names that are listed in a table and create one view that has all of the info listed. I have the cursor set, I have the base of the view set, but how would I modify the syntax to add a UNION ALL statement instead of repeating the Create View statement?

This is the syntax I have:

Create Table dbo.tabletoprocess
    ,DatabaseName varchar(100) NOT NULL
    ,TablePrefix varchar(25) NOT NULL
    ,ActiveCustomer varchar(50) NOT NULL

Insert Into dbo.tabletoprocess (DatabaseName, TablePrefix, ActiveCustomer) VALUES
('arc', 'night91_', 'yes')
,('bell', 'abc123_', 'yes')
,('fed', 'fred13_', 'yes')
,('rea', 'bad12_', 'yes')
,('bva', 'red121_', 'yes')
,('sez', 'bc12_', 'yes')

Declare @dbname varchar(100), @tableprefix varchar(100), @sql nvarchar(max), @fullpre varchar(500)


    Select databasename, tableprefix
    FROM tabletoprocess 
    WHERE ActiveCustomer = 'yes'

    FETCH NEXT FROM DB_CURSOR INTO @dbname, @tableprefix

    Set @fullpre = @dbname + '.' + @tableprefix


        Set @sql = 'Create View dbo.Test As 
                    Select *                        
                        FROM OPENQUERY(linkedserver, ''Select op.field1, op.field2, o.field3, a.field4, 
                                                a.field5, a.field6, a.field7, a.field8, a.field9, 
                                                o.field10, o.field11 
                                                FROM '+@fullpre+'orderinfo as op 
                                                LEFT JOIN '+@fullpre+'order as o ON op.oi=o.oi 
                                                LEFT JOIN '+@fullpre+'address as a
                                                ON o.osa_id=a.ai'')';
        Print @sql

         FETCH NEXT FROM DB_CURSOR INTO @dbname, @tableprefix


  • Are you trying to use UNION ALL in order to create multiple views with one statement? If so, that's not what UNION ALL is for. Or are you trying to create one view for multiple tables? – MguerraTorres Aug 16 '17 at 14:36
  • @MguerraTorres - one view for multiple tables – Yohan Greenburg Aug 16 '17 at 14:37
  • Ahh, then yeah you're good to go. Just add the UNION ALL between the a.ai and the first single quote at the end. Your issue is actually getting the additional table names. You'll have to make the cursor set @sql += ' UNION ALL... ' – MguerraTorres Aug 16 '17 at 14:41
  • and where would my exec @sql statement go? As it sits currently, each statement printed begins with Create View.... – Yohan Greenburg Aug 16 '17 at 14:43
  • Use the cursor to keep adding to @sql. Then after the cursor completes you EXECUTE the sql variable. – MguerraTorres Aug 16 '17 at 14:45

Have you tried modifying the set command like such:

Set @sql = isnull(@sql,'') +
           case when @sql is NULL
                then ' create view dbo.Test as ' 
                else ' union all '
           end +
           'SELECT ...'

Obviously (?) the print @sql (and exec @sql) is issued after the cursor loop has been completed/closed.

Here's a sample fiddle showing the results:

declare @sql varchar(max), @cr char(1)
set @cr = char(10)

Set @sql = isnull(@sql,'') + @cr +
           case when @sql is NULL
                then 'create view dbo.Test as ' 
                else 'union all '
           end + @cr +
           'SELECT x from mytable1 ...'

Set @sql = isnull(@sql,'') + @cr +
           case when @sql is NULL
                then 'create view dbo.Test as ' 
                else 'union all '
           end + @cr +
           'SELECT y from mytable2 ...'

Set @sql = isnull(@sql,'') + @cr +
           case when @sql is NULL
                then 'create view dbo.Test as ' 
                else 'union all '
           end + @cr +
           'SELECT z from mytable3 ...'

print @sql

And the results of running the above:

create view dbo.Test as 
SELECT x from mytable1 ...
union all 
SELECT y from mytable2 ...
union all 
SELECT z from mytable3 ...
| improve this answer | |
  • If I modify my syntax to be such as above nothing is printed from my print @sql statement? – Yohan Greenburg Aug 16 '17 at 14:49
  • ah, forgot MS SQL doesn't like to append null values to non-null values; have updated my answer (+ isnull(@sql,'')) ... see if that works now? – markp-fuso Aug 16 '17 at 14:51
  • That is printing the Create View statement before each iteration – Yohan Greenburg Aug 16 '17 at 14:53
  • You'll need to show us the code you've written; I've added a dbfiddle to my answer that shows the proposed code working as desired. – markp-fuso Aug 16 '17 at 14:57
  • The code I am using is in my OP or you can view it here sqlfiddle.com/#!6/a1c136/1 – Yohan Greenburg Aug 16 '17 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.