Question :
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
(
id int IDENTITY(1,1) PRIMARY KEY NOT NULL
,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)
Declare DB_CURSOR CURSOR FOR
Select databasename, tableprefix
FROM tabletoprocess
WHERE ActiveCustomer = 'yes'
OPEN DB_CURSOR
FETCH NEXT FROM DB_CURSOR INTO @dbname, @tableprefix
Set @fullpre = @dbname + '.' + @tableprefix
WHILE @@FETCH_STATUS = 0
BEGIN
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
END
CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR
Answer :
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 ...