Hi all,
Iam aware that #Temporary tables can not be used in creation of views, but
iam in a situation where in iam needed to do so.
can anybody provide me with a workaround for this, its very urgent....any
help would be highly appreciated.
Thanks in advance,
Kishore> Iam aware that #Temporary tables can not be used in creation of views, but
> iam in a situation where in iam needed to do so.
Can you elaborate? Why do you need a temporary table and a permanent view
of that temporary table? This sounds like buying a house in Florida because
you're going there for spring break.|||Well, you can't do that.
So if that view that you want is something you will be using in all queries,
then select the required rows and insert into another temporary table and
use this assuming you are using a view.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uWPH%23AedGHA.3632@.TK2MSFTNGP05.phx.gbl...
> This sounds like buying a house in Florida because
> you're going there for spring break.
Aaron,
One of these days I am going to put together a compilation of these
analogies of yours. You have a particular knack for them.
I'm not being sarcastic at all. You always manage to come up with a
comparison that suits the situation perfectly.|||Hi,
Thanks for your prompt reply.
As asked by you, i will try to elaborate a bit more, please pardon me for
this lengthy explanation as i am in very urgent need of it, any help would b
e
highly appreciated.
My Business need requires that i need to create a table whose columns names
are taken from data contained in different tables, in essence iam creating a
table dynamically, which will have different number of columns in each run.
For efficeincey purposes, my customers earlier implementation uses Temporary
tables.
finally that dynamic #Temptable is filled up with data and a select
statement applied on it returns the needed data in XML format.
Then they came across the SQL Server row size limit of some 8060 bytes,
since each of the column in my dynamic table is of type NVARCHAR(255), at
maximum i can add 15 columns only to it. so instead of having only single
table, i decided to host those columns in multiple tables, i mean 1-15
columns will be added to #temptable-1 then 16-30 columns will be added to
#temptable-2 and so on....
so that problem has been addressed, but the output of the final select
statement that works on the dynamic #Temptable is creating problem...earlie
r
there was only one table, but now there can be 'n' number of tables dependin
g
on the number of columns to be added, so the XML resulting from the select
statement on these 'n' number of dynamic #Temptables is different from the
earlier on when there was only one dynamic #Temptable, but i need to maintai
n
that format due to some other dependencies.
so i thought of creating a single view(since a table can not hold all the
columns resulting from the select statement on those 'n' number of tables)
from all those #Temptables and then generate XML from that single
view...which will be similar in syntax to the earlier case when there was
only a single #Temptable.
Hope this explains...
Thanks & Regards,
Kishore
"Aaron Bertrand [SQL Server MVP]" wrote:
> Can you elaborate? Why do you need a temporary table and a permanent view
> of that temporary table? This sounds like buying a house in Florida becau
se
> you're going there for spring break.
>
>|||becuase of the SQL Server 2000 row size limit of 8060 bytes i can put all my
columns taked from other #Temptables into a new #Temptable to be used as a
view as suggested by you, Total size of all my columns exceeds tht limit so
table option is ruled out.
"Omnibuzz" wrote:
> Well, you can't do that.
> So if that view that you want is something you will be using in all querie
s,
> then select the required rows and insert into another temporary table and
> use this assuming you are using a view.|||Mr Aaron,
have you seen my explanation, can you suggest me anything.
-Kishore
"Aaron Bertrand [SQL Server MVP]" wrote:
> Can you elaborate? Why do you need a temporary table and a permanent view
> of that temporary table? This sounds like buying a house in Florida becau
se
> you're going there for spring break.
>
>
sql
