Query

my query looks like this
select a,c,d
from aaa
where some condtion
union all
select a,c,d
from bbbb
where some condtion
how to put the result into temp. table
thanks
kalyan
Hi,
Use the below query format:-
Select * into #temp1 FROM (Select i from X11 Union ALL Select i from X12) K
Thanks
Hari
SQL Server MVP
"Kalyan" <Kalyan@.discussions.microsoft.com> wrote in message
news:42EA9755-D5C3-47F8-97A2-A4C1E6CBB4D1@.microsoft.com...
> my query looks like this
> select a,c,d
> from aaa
> where some condtion
> union all
> select a,c,d
> from bbbb
> where some condtion
> how to put the result into temp. table
>
> thanks
> kalyan
>

Query

my query looks like this
select a,c,d
from aaa
where some condtion
union all
select a,c,d
from bbbb
where some condtion
how to put the result into temp. table
thanks
kalyanHi,
Use the below query format:-
Select * into #temp1 FROM (Select i from X11 Union ALL Select i from X12) K
Thanks
Hari
SQL Server MVP
"Kalyan" <Kalyan@.discussions.microsoft.com> wrote in message
news:42EA9755-D5C3-47F8-97A2-A4C1E6CBB4D1@.microsoft.com...
> my query looks like this
> select a,c,d
> from aaa
> where some condtion
> union all
> select a,c,d
> from bbbb
> where some condtion
> how to put the result into temp. table
>
> thanks
> kalyan
>

Quastion about Temp Tables

I'm relatively new to SQL-Server, and I have a quick question about
temporary tables. I am using a function from Erland Sommarskog's
excellent article about arrays in SQL server.

Erland's approach uses a user-defined function, which parses a string
of delimited values (integers) into a temporary table. The temporary
table is joined to tables in a stored procedure select statement to
limit the records returned.

The question is, what happens to the temporary table generated by the
function? Is it implicitly destroyed when the stored procedure
finishes? Does it need to be dropped explicitly (something not done in
his examples)?

Also, what are the implications of scope, if multiple users are
accessing the stored procedure through a web application?

Thanks in advance,

TimA temporary table created within a stored procedure is automatically dropped
when the procedure exists.

On 12 Feb 2004 12:59:53 -0800, tim.pascoe@.cciw.ca (Tim Pascoe) wrote:

>I'm relatively new to SQL-Server, and I have a quick question about
>temporary tables. I am using a function from Erland Sommarskog's
>excellent article about arrays in SQL server.
>Erland's approach uses a user-defined function, which parses a string
>of delimited values (integers) into a temporary table. The temporary
>table is joined to tables in a stored procedure select statement to
>limit the records returned.
>The question is, what happens to the temporary table generated by the
>function? Is it implicitly destroyed when the stored procedure
>finishes? Does it need to be dropped explicitly (something not done in
>his examples)?
>Also, what are the implications of scope, if multiple users are
>accessing the stored procedure through a web application?
>Thanks in advance,
>Tim|||[posted and mailed, please reply in news]

Tim Pascoe (tim.pascoe@.cciw.ca) writes:
> I'm relatively new to SQL-Server, and I have a quick question about
> temporary tables. I am using a function from Erland Sommarskog's
> excellent article about arrays in SQL server.

Glad to hear that you liked it!

> Erland's approach uses a user-defined function, which parses a string
> of delimited values (integers) into a temporary table. The temporary
> table is joined to tables in a stored procedure select statement to
> limit the records returned.
> The question is, what happens to the temporary table generated by the
> function? Is it implicitly destroyed when the stored procedure
> finishes? Does it need to be dropped explicitly (something not done in
> his examples)?

If you are using the version with the user-defined function, the table
is never stored on disk (at least conceptually), so you don't have to
bother.

If you are using the version where a stored procedure fills in a temp
table that was created prior to the call, that table is stored in disk.
However, as Steve J pointed out, a temp table disappears when the scope
it was created in goes away, so neither in this case you need to worry.

> Also, what are the implications of scope, if multiple users are
> accessing the stored procedure through a web application?

There are no issues with this. A temp table of the # variety are private
to the connection that created it. This applies as well to the return
table created by multi-step function.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

qualifying table variables

hi all!

I am using table variables instead of creating a temp table because it seems to be faster

But now I need qualify the table variable so I can join it with another table having a field with same name of a field from the table variable. U know if I can do that?

ex: with temp table

create table #tmp... (F1...)
#tmp.f1

with table variable

declare @.temp table(...
@.table.f1 - can´t do it

the first question is if I can join the table variable with another table and how to do that qualifying the variable table, that is, putting the name of the var temp with the field, because the other table has a field with same name

thank to all and happiness for all 2004Hi all

I think I found the solution

It is not possible to qualify the talble variable because it is not part of a persistent table, so I cant assing table variables,

I saw it in

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_66w5.asp

it is the same as in sql 2000 BOL

thanks. so I have to use a temp table to qualify it if I want to join the table. My thinking is that I can join table variables

hope it is useful to u|||Hi all!

Thanks for having the time to read it

I was wrong and found the solution.

I can join and qualify a table var, only aliasing the table as in

insert into @.table... join temp.field...
from @.vartemp temp

--

here temp is the alias of @.vartemp, so I can use this alias instead of @.vartemp when, for example, there are two fields with same name in two tables, and one is @.vartemp

Now, once solved it, my problem is with UPDATETEXT. I am updating a text field and need to qualify the @.vartable in order to update it using a pointer to the text field. Since there are not any FROM clause as above, I dont know where to qualify the @.vartable (tried qualifying in DECLARE, but cant do it there)

UPDATETEXT @.vartemp.pointer - cant use @.vartemp, need an alias, but where to give the alias?

Thanks all!