Hi,
I have got a requirement to create a dynamic view which union all the data from different dadtabases (same table, we are pulling the data).
for ex: i have a table "A" in say 5 databases. i need create a dynamic view which union all the data from all the database.
select * from A (from (database1) unnion select * from A (from (database2)..... etc.
tried to create through dynamic SP by first selecting all the databases. but throws error saying that create view must be the first statement. Can anybody please help
alter proc test
as begin
declare @query as nvarchar(max)
set @query='declare #table as table (Name varchar(10),dept varchar(10),mark int)
insert into #table
select * from test1 go create view b as select * from @table'print @query
exec sp_executesql @query
select * from b
end
