3 subjects for the same story !!!

3 subjects for the same story !!!

Post by Dan » Tue, 08 Jun 1999 04:00:00



Sorry for possible misunderstandings.

Please review the subjects (all of them are related):
1. Select into
2. GO and Stored Procedure
3. Backward Compatibility Details (Level 4) drive me crazy

The "Select into" story started when:

I was trying to execute the below statements:

I have tried to select several times from different tables into the same
table (without dropping it every time).

i.e.

Select ... into <dest> from <a>
[ I don't want to issue drop table <dest>]
Select ... into <dest> from <b>
[ I don't want to issue drop table <dest>]
Select ... into <dest> from <c>
[ I don't want to issue drop table <dest>]
Select ... into <dest> from <d>
[ I don't want to issue drop table <dest>]

Note:
All those statements should be in the same batch. <dest> should be the same
table.
It is any solution ?.

The "GO and Stored Procedure" story continued when:
I was trying to execute the above lines in a Stored procedure. It doesn't
even parse and generate an error like "There is already on object named
<dest> in the database".
I mention that even if I have used "drop table" before the next "select
into" statement didn't work.
Any idea, solution ?.

The story ended with the "Backward Compatibility Details (Level 4)" issue
from BOL which drive me crazy because it doesn't work what they are saying
!!!.

I appreciate any help.

--
All the best,

Daniel Gavrilescu

 
 
 

3 subjects for the same story !!!

Post by Roy Harv » Tue, 08 Jun 1999 04:00:00


Dan,

Quote:>Select ... into <dest> from <a>
>[ I don't want to issue drop table <dest>]
>Select ... into <dest> from <b>
>[ I don't want to issue drop table <dest>]
>Select ... into <dest> from <c>
>[ I don't want to issue drop table <dest>]
>Select ... into <dest> from <d>
>[ I don't want to issue drop table <dest>]

>Note:
>All those statements should be in the same batch. <dest> should be the same
>table.

SELECT INTO creates a table.  You can only create it once, so you can
only perform one SELECT INTO.  Your alternatives are:

 1) Use SELECT INTO for the first, then use INSERT <dest> SELECT...
for the rest.

 2) Run all the SELECTs as a single command using UNION ALL:
    Select ... into <dest> from <a>
    UNION ALL
    Select ... into <dest> from <b>
    UNION ALL
    Select ... into <dest> from <c>
    UNION ALL
    Select ... into <dest> from <d>

Quote:>The "GO and Stored Procedure" story continued when:
>I was trying to execute the above lines in a Stored procedure. It doesn't
>even parse and generate an error like "There is already on object named
><dest> in the database".
>I mention that even if I have used "drop table" before the next "select
>into" statement didn't work.

If you are writting code that will end up in a stored procedure, do
not use any GO commands (except on at the end), and run the script as
a single piece, not in section.

Quote:>The story ended with the "Backward Compatibility Details (Level 4)" issue
>from BOL which drive me crazy because it doesn't work what they are saying
>!!!.

There are 22 sub-headings under this, and I see nothing in your
message about what your problem with this is.

Roy

 
 
 

3 subjects for the same story !!!

Post by Neil Pik » Tue, 08 Jun 1999 04:00:00


Dan - I suggest you re-formulate what I can only guess are three separate posts
into a single one with all the relevant info in and then maybe people can help

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see
 http://go.compuserve.com/sqlserver (library 1) - latest stuff is always here
 www.ntfaq.com/sql.html
 http://www.swynk.com/faq/sql/sqlserverfaq.asp

 
 
 

3 subjects for the same story !!!

Post by Dan » Wed, 09 Jun 1999 04:00:00


To be precise from the "Backward Compatibility Details (Level 4)" issue
from BOL I was refering to CREATE PROCEDURE (Level 4) link.
This is states that I cannot execute in the same SP the next sequence:

Select ... into <dest> from <a>
drop table <dest>
Select ... into <dest> from <b>

It's generates a parse error like " the object<dest> allready exist.

Is there any way to avoid this ?.


Quote:

>Dan,

>>Select ... into <dest> from <a>
>>[ I don't want to issue drop table <dest>]
>>Select ... into <dest> from <b>
>>[ I don't want to issue drop table <dest>]
>>Select ... into <dest> from <c>
>>[ I don't want to issue drop table <dest>]
>>Select ... into <dest> from <d>
>>[ I don't want to issue drop table <dest>]

>>Note:
>>All those statements should be in the same batch. <dest> should be the
same
>>table.

>SELECT INTO creates a table.  You can only create it once, so you can
>only perform one SELECT INTO.  Your alternatives are:

> 1) Use SELECT INTO for the first, then use INSERT <dest> SELECT...
>for the rest.

> 2) Run all the SELECTs as a single command using UNION ALL:
>    Select ... into <dest> from <a>
>    UNION ALL
>    Select ... into <dest> from <b>
>    UNION ALL
>    Select ... into <dest> from <c>
>    UNION ALL
>    Select ... into <dest> from <d>

>>The "GO and Stored Procedure" story continued when:
>>I was trying to execute the above lines in a Stored procedure. It doesn't
>>even parse and generate an error like "There is already on object named
>><dest> in the database".
>>I mention that even if I have used "drop table" before the next "select
>>into" statement didn't work.

>If you are writting code that will end up in a stored procedure, do
>not use any GO commands (except on at the end), and run the script as
>a single piece, not in section.

>>The story ended with the "Backward Compatibility Details (Level 4)" issue
>>from BOL which drive me crazy because it doesn't work what they are saying
>>!!!.

>There are 22 sub-headings under this, and I see nothing in your
>message about what your problem with this is.

>Roy

 
 
 

3 subjects for the same story !!!

Post by Dan » Wed, 09 Jun 1999 04:00:00


To be precise from the "Backward Compatibility Details (Level 4)" issue
from BOL I was refering to CREATE PROCEDURE (Level 4) link.
This is states that I cannot execute in the same SP the next sequence:

Select ... into <dest> from <a>
drop table <dest>
Select ... into <dest> from <b>

It's generates a parse error like " the object<dest> allready exist.

Is there any way to avoid this ?.


>Sorry for possible misunderstandings.

>Please review the subjects (all of them are related):
>1. Select into
>2. GO and Stored Procedure
>3. Backward Compatibility Details (Level 4) drive me crazy

>The "Select into" story started when:

>I was trying to execute the below statements:

>I have tried to select several times from different tables into the same
>table (without dropping it every time).

>i.e.

>Select ... into <dest> from <a>
>[ I don't want to issue drop table <dest>]
>Select ... into <dest> from <b>
>[ I don't want to issue drop table <dest>]
>Select ... into <dest> from <c>
>[ I don't want to issue drop table <dest>]
>Select ... into <dest> from <d>
>[ I don't want to issue drop table <dest>]

>Note:
>All those statements should be in the same batch. <dest> should be the same
>table.
>It is any solution ?.

>The "GO and Stored Procedure" story continued when:
>I was trying to execute the above lines in a Stored procedure. It doesn't
>even parse and generate an error like "There is already on object named
><dest> in the database".
>I mention that even if I have used "drop table" before the next "select
>into" statement didn't work.
>Any idea, solution ?.

>The story ended with the "Backward Compatibility Details (Level 4)" issue
>from BOL which drive me crazy because it doesn't work what they are saying
>!!!.

>I appreciate any help.

>--
>All the best,

>Daniel Gavrilescu

 
 
 

3 subjects for the same story !!!

Post by Roy Harv » Thu, 10 Jun 1999 04:00:00


Dan,

Quote:>To be precise from the "Backward Compatibility Details (Level 4)" issue
>from BOL I was refering to CREATE PROCEDURE (Level 4) link.
>This is states that I cannot execute in the same SP the next sequence:

>Select ... into <dest> from <a>
>drop table <dest>
>Select ... into <dest> from <b>

>It's generates a parse error like " the object<dest> allready exist.

>Is there any way to avoid this ?.

Is there any reason why all the temp tables have to have the same
name?  Could you get along with:

Select ... into <dest1> from <a>
drop table <dest1>
Select ... into <dest2> from <b>

Roy

 
 
 

3 subjects for the same story !!!

Post by Dan » Thu, 10 Jun 1999 04:00:00


Unfortunately no !.

Thank for your advice.
Actually I have used the alternative with
"...
select * into #tmp1 from slsdist
drop table #tmp1
select * into #tmp1 from slsterr
..."

Unfortunately it doesn't work inside a stored procedure. Generate error at
parsing time.
"There is already an object named '#tmp1' in the database."
Any tips ?.


>Dan,

>>To be precise from the "Backward Compatibility Details (Level 4)" issue
>>from BOL I was refering to CREATE PROCEDURE (Level 4) link.
>>This is states that I cannot execute in the same SP the next sequence:

>>Select ... into <dest> from <a>
>>drop table <dest>
>>Select ... into <dest> from <b>

>>It's generates a parse error like " the object<dest> allready exist.

>>Is there any way to avoid this ?.

>Is there any reason why all the temp tables have to have the same
>name?  Could you get along with:

>Select ... into <dest1> from <a>
>drop table <dest1>
>Select ... into <dest2> from <b>

>Roy

 
 
 

3 subjects for the same story !!!

Post by Rainer Flentg » Thu, 24 Jun 1999 04:00:00


Try to use single Transactions like this one:

Select ... into <dest> from <a>
go
drop table <dest>
go
Select ... into <dest> from <b>
go


> Dan,

> >To be precise from the "Backward Compatibility Details (Level 4)" issue
> >from BOL I was refering to CREATE PROCEDURE (Level 4) link.
> >This is states that I cannot execute in the same SP the next sequence:

> >Select ... into <dest> from <a>
> >drop table <dest>
> >Select ... into <dest> from <b>

> >It's generates a parse error like " the object<dest> allready exist.

> >Is there any way to avoid this ?.

> Is there any reason why all the temp tables have to have the same
> name?  Could you get along with:

> Select ... into <dest1> from <a>
> drop table <dest1>
> Select ... into <dest2> from <b>

> Roy

 
 
 

3 subjects for the same story !!!

Post by Dan » Wed, 09 Jun 1999 04:00:00


To be precise from the "Backward Compatibility Details (Level 4)" issue
from BOL I was refering to CREATE PROCEDURE (Level 4) link.
This is states that I cannot execute in the same SP the next sequence:

Select ... into <dest> from <a>
drop table <dest>
Select ... into <dest> from <b>

It's generates a parse error like " the object<dest> allready exist.

Is there any way to avoid this ?.


>Sorry for possible misunderstandings.

>Please review the subjects (all of them are related):
>1. Select into
>2. GO and Stored Procedure
>3. Backward Compatibility Details (Level 4) drive me crazy

>The "Select into" story started when:

>I was trying to execute the below statements:

>I have tried to select several times from different tables into the same
>table (without dropping it every time).

>i.e.

>Select ... into <dest> from <a>
>[ I don't want to issue drop table <dest>]
>Select ... into <dest> from <b>
>[ I don't want to issue drop table <dest>]
>Select ... into <dest> from <c>
>[ I don't want to issue drop table <dest>]
>Select ... into <dest> from <d>
>[ I don't want to issue drop table <dest>]

>Note:
>All those statements should be in the same batch. <dest> should be the same
>table.
>It is any solution ?.

>The "GO and Stored Procedure" story continued when:
>I was trying to execute the above lines in a Stored procedure. It doesn't
>even parse and generate an error like "There is already on object named
><dest> in the database".
>I mention that even if I have used "drop table" before the next "select
>into" statement didn't work.
>Any idea, solution ?.

>The story ended with the "Backward Compatibility Details (Level 4)" issue
>from BOL which drive me crazy because it doesn't work what they are saying
>!!!.

>I appreciate any help.

>--
>All the best,

>Daniel Gavrilescu

 
 
 

3 subjects for the same story !!!

Post by Roy Harv » Thu, 10 Jun 1999 04:00:00


Dan,

Quote:>To be precise from the "Backward Compatibility Details (Level 4)" issue
>from BOL I was refering to CREATE PROCEDURE (Level 4) link.
>This is states that I cannot execute in the same SP the next sequence:

>Select ... into <dest> from <a>
>drop table <dest>
>Select ... into <dest> from <b>

>It's generates a parse error like " the object<dest> allready exist.

>Is there any way to avoid this ?.

Is there any reason why all the temp tables have to have the same
name?  Could you get along with:

Select ... into <dest1> from <a>
drop table <dest1>
Select ... into <dest2> from <b>

Roy

 
 
 

3 subjects for the same story !!!

Post by Dan » Thu, 10 Jun 1999 04:00:00


Thank for your advice.
Actually I have used the alternative with
"...
select * into #tmp1 from slsdist
drop table #tmp1
select * into #tmp1 from slsterr
..."

Unfortunately it doesn't work inside a stored procedure. Generate error at
parsing time.
"There is already an object named '#tmp1' in the database."
Any tips ?.


>Dan,

>You simply cannot use SELECT INTO to create the same table repeatedly
unless
>you explicitly drop it before the next SELECT INTO or you do what I
>suggested before, SELECT INTO the first time and then INSERT SELECT.

>For each SELECT INTO, will the <dest> table have a different structure,
i.e.
>different fields? And if you want to save space by not having multiple
>temporary tables, then drop the table each time.

>As far as the "Backward Compatibility Details (Level 4)" CREATE PROCEDURE,
>I'm sorry but I can't help you there, I haven't tried any of this myself.
>Maybe Neil Pike or one of the other MVPs can help you there.

>--
>Mike MacGregor
>Principal Consultant
>TygerCon Inc.
>Tel: 519-579-5276
>Fax: 519-579-7360
>Cell: 416-568-4805

>www.tygercon.com



>> I don't want to use insert because the <dest> table should be recreated
>from
>> the scratch each time I am executing select statements.
>> It's true that I can use different <dest> names for each select, but I
>want
>> to save space by using the same <dest > table

>> Related to the "Backward Compatibility Details (Level 4)" CREATE
PROCEDURE
>> (Level 4) paragraph is stated that in pre-release version:"CREATE
>PROCEDURE
>> statements failed if they contained a CREATE TABLE or SELECT INTO
>statement
>> creating a temporary table with the same name as a temporary table that
>> existed at the time the CREATE PROCEDURE statement was executed."

>> ... and in final version:"The CREATE PROCEDURE statement succeeds."

>> ... but for me it doesn't work ( i have also service pack 1 installed).


>> >First off, SELECT INTO will create the table you are SELECTing into and
>> >cannot be executed a second time against the same table. Use INSERT
><dest>
>> >SELECT from <a> instead after the first SELECT INTO.

>> >You cannot put GO within a Stored Procedure: GO is a batch command
>> >terminator and is not strictly a SQL command at all but is understood by
>> >software such as ISQL, ISQLW, Query Analyzer, etc..

>> >>> The story ended with the "Backward Compatibility Details (Level 4)"
>> issue
>> >> from BOL which drive me crazy because it doesn't work what they are
>> saying
>> >> !!!.<

>> >Sorry but I don't understand the question for the last part! Can you
>> provide
>> >more details?

>> >--
>> >Mike MacGregor
>> >Principal Consultant
>> >TygerCon Inc.
>> >Tel: 519-579-5276
>> >Fax: 519-579-7360
>> >Cell: 416-568-4805

>> >www.tygercon.com



>> >> Sorry for possible misunderstandings.

>> >> Please review the subjects (all of them are related):
>> >> 1. Select into
>> >> 2. GO and Stored Procedure
>> >> 3. Backward Compatibility Details (Level 4) drive me crazy

>> >> The "Select into" story started when:

>> >> I was trying to execute the below statements:

>> >> I have tried to select several times from different tables into the
>same
>> >> table (without dropping it every time).

>> >> i.e.

>> >> Select ... into <dest> from <a>
>> >> [ I don't want to issue drop table <dest>]
>> >> Select ... into <dest> from <b>
>> >> [ I don't want to issue drop table <dest>]
>> >> Select ... into <dest> from <c>
>> >> [ I don't want to issue drop table <dest>]
>> >> Select ... into <dest> from <d>
>> >> [ I don't want to issue drop table <dest>]

>> >> Note:
>> >> All those statements should be in the same batch. <dest> should be the
>> >same
>> >> table.
>> >> It is any solution ?.

>> >> The "GO and Stored Procedure" story continued when:
>> >> I was trying to execute the above lines in a Stored procedure. It
>doesn't
>> >> even parse and generate an error like "There is already on object
named
>> >> <dest> in the database".
>> >> I mention that even if I have used "drop table" before the next
"select
>> >> into" statement didn't work.
>> >> Any idea, solution ?.

>> >> The story ended with the "Backward Compatibility Details (Level 4)"
>issue
>> >> from BOL which drive me crazy because it doesn't work what they are
>> saying
>> >> !!!.

>> >> I appreciate any help.

>> >> --
>> >> All the best,

>> >> Daniel Gavrilescu

 
 
 

3 subjects for the same story !!!

Post by Dan » Thu, 10 Jun 1999 04:00:00


Unfortunately no !.

Thank for your advice.
Actually I have used the alternative with
"...
select * into #tmp1 from slsdist
drop table #tmp1
select * into #tmp1 from slsterr
..."

Unfortunately it doesn't work inside a stored procedure. Generate error at
parsing time.
"There is already an object named '#tmp1' in the database."
Any tips ?.


>Dan,

>>To be precise from the "Backward Compatibility Details (Level 4)" issue
>>from BOL I was refering to CREATE PROCEDURE (Level 4) link.
>>This is states that I cannot execute in the same SP the next sequence:

>>Select ... into <dest> from <a>
>>drop table <dest>
>>Select ... into <dest> from <b>

>>It's generates a parse error like " the object<dest> allready exist.

>>Is there any way to avoid this ?.

>Is there any reason why all the temp tables have to have the same
>name?  Could you get along with:

>Select ... into <dest1> from <a>
>drop table <dest1>
>Select ... into <dest2> from <b>

>Roy

 
 
 

3 subjects for the same story !!!

Post by Mike MacGrego » Thu, 10 Jun 1999 04:00:00


Dan,

No it won't work actually because a Stored Proc is a batch and so the DROP
TABLE doesn't occur instantaneously but rather at SP execution completion.

Sorry, I think you'll have to bite the bullet here and create multiple temp
tables.

Why are you so reluctant to do this? You mentioned about saving space but is
it really at that much of a premium for you? What exactly are you trying to
achieve with all this anyway? Maybe there's a better approach overall.

--
Mike MacGregor
Principal Consultant
TygerCon Inc.
Tel: 519-579-5276
Fax: 519-579-7360
Cell: 416-568-4805

www.tygercon.com

> Thank for your advice.
> Actually I have used the alternative with
> "...
> select * into #tmp1 from slsdist
> drop table #tmp1
> select * into #tmp1 from slsterr
> ..."

> Unfortunately it doesn't work inside a stored procedure. Generate error at
> parsing time.
> "There is already an object named '#tmp1' in the database."
> Any tips ?.


> >Dan,

> >You simply cannot use SELECT INTO to create the same table repeatedly
> unless
> >you explicitly drop it before the next SELECT INTO or you do what I
> >suggested before, SELECT INTO the first time and then INSERT SELECT.

> >For each SELECT INTO, will the <dest> table have a different structure,
> i.e.
> >different fields? And if you want to save space by not having multiple
> >temporary tables, then drop the table each time.

> >As far as the "Backward Compatibility Details (Level 4)" CREATE
PROCEDURE,
> >I'm sorry but I can't help you there, I haven't tried any of this myself.
> >Maybe Neil Pike or one of the other MVPs can help you there.

> >--
> >Mike MacGregor
> >Principal Consultant
> >TygerCon Inc.
> >Tel: 519-579-5276
> >Fax: 519-579-7360
> >Cell: 416-568-4805

> >www.tygercon.com



> >> I don't want to use insert because the <dest> table should be recreated
> >from
> >> the scratch each time I am executing select statements.
> >> It's true that I can use different <dest> names for each select, but I
> >want
> >> to save space by using the same <dest > table

> >> Related to the "Backward Compatibility Details (Level 4)" CREATE
> PROCEDURE
> >> (Level 4) paragraph is stated that in pre-release version:"CREATE
> >PROCEDURE
> >> statements failed if they contained a CREATE TABLE or SELECT INTO
> >statement
> >> creating a temporary table with the same name as a temporary table that
> >> existed at the time the CREATE PROCEDURE statement was executed."

> >> ... and in final version:"The CREATE PROCEDURE statement succeeds."

> >> ... but for me it doesn't work ( i have also service pack 1 installed).


> >> >First off, SELECT INTO will create the table you are SELECTing into
and
> >> >cannot be executed a second time against the same table. Use INSERT
> ><dest>
> >> >SELECT from <a> instead after the first SELECT INTO.

> >> >You cannot put GO within a Stored Procedure: GO is a batch command
> >> >terminator and is not strictly a SQL command at all but is understood
by
> >> >software such as ISQL, ISQLW, Query Analyzer, etc..

> >> >>> The story ended with the "Backward Compatibility Details (Level 4)"
> >> issue
> >> >> from BOL which drive me crazy because it doesn't work what they are
> >> saying
> >> >> !!!.<

> >> >Sorry but I don't understand the question for the last part! Can you
> >> provide
> >> >more details?

> >> >--
> >> >Mike MacGregor
> >> >Principal Consultant
> >> >TygerCon Inc.
> >> >Tel: 519-579-5276
> >> >Fax: 519-579-7360
> >> >Cell: 416-568-4805

> >> >www.tygercon.com



> >> >> Sorry for possible misunderstandings.

> >> >> Please review the subjects (all of them are related):
> >> >> 1. Select into
> >> >> 2. GO and Stored Procedure
> >> >> 3. Backward Compatibility Details (Level 4) drive me crazy

> >> >> The "Select into" story started when:

> >> >> I was trying to execute the below statements:

> >> >> I have tried to select several times from different tables into the
> >same
> >> >> table (without dropping it every time).

> >> >> i.e.

> >> >> Select ... into <dest> from <a>
> >> >> [ I don't want to issue drop table <dest>]
> >> >> Select ... into <dest> from <b>
> >> >> [ I don't want to issue drop table <dest>]
> >> >> Select ... into <dest> from <c>
> >> >> [ I don't want to issue drop table <dest>]
> >> >> Select ... into <dest> from <d>
> >> >> [ I don't want to issue drop table <dest>]

> >> >> Note:
> >> >> All those statements should be in the same batch. <dest> should be
the
> >> >same
> >> >> table.
> >> >> It is any solution ?.

> >> >> The "GO and Stored Procedure" story continued when:
> >> >> I was trying to execute the above lines in a Stored procedure. It
> >doesn't
> >> >> even parse and generate an error like "There is already on object
> named
> >> >> <dest> in the database".
> >> >> I mention that even if I have used "drop table" before the next
> "select
> >> >> into" statement didn't work.
> >> >> Any idea, solution ?.

> >> >> The story ended with the "Backward Compatibility Details (Level 4)"
> >issue
> >> >> from BOL which drive me crazy because it doesn't work what they are
> >> saying
> >> >> !!!.

> >> >> I appreciate any help.

> >> >> --
> >> >> All the best,

> >> >> Daniel Gavrilescu

 
 
 

3 subjects for the same story !!!

Post by Dan » Fri, 11 Jun 1999 04:00:00


Seems that I must use multiple temp tables.
The reason I am doing this is:
- I Have migrated SQL statements written in FoxPRo 2.6 and I want to make
minimum changes in the code.  There are a lot of statements to be changed in
order to avoid using the same temp table.
- The temp table has different structure and sometime has a big size
(vary from 25Kb to 300 Mb).
- I would like to use the queries from the stored procedure, is the best
approach from performance point of view and I should use also Crystal Report
to run reports based on some SQL tables.


>Dan,

>No it won't work actually because a Stored Proc is a batch and so the DROP
>TABLE doesn't occur instantaneously but rather at SP execution completion.

>Sorry, I think you'll have to bite the bullet here and create multiple temp
>tables.

>Why are you so reluctant to do this? You mentioned about saving space but
is
>it really at that much of a premium for you? What exactly are you trying to
>achieve with all this anyway? Maybe there's a better approach overall.

>--
>Mike MacGregor
>Principal Consultant
>TygerCon Inc.
>Tel: 519-579-5276
>Fax: 519-579-7360
>Cell: 416-568-4805

>www.tygercon.com


>> Thank for your advice.
>> Actually I have used the alternative with
>> "...
>> select * into #tmp1 from slsdist
>> drop table #tmp1
>> select * into #tmp1 from slsterr
>> ..."

>> Unfortunately it doesn't work inside a stored procedure. Generate error
at
>> parsing time.
>> "There is already an object named '#tmp1' in the database."
>> Any tips ?.


>> >Dan,

>> >You simply cannot use SELECT INTO to create the same table repeatedly
>> unless
>> >you explicitly drop it before the next SELECT INTO or you do what I
>> >suggested before, SELECT INTO the first time and then INSERT SELECT.

>> >For each SELECT INTO, will the <dest> table have a different structure,
>> i.e.
>> >different fields? And if you want to save space by not having multiple
>> >temporary tables, then drop the table each time.

>> >As far as the "Backward Compatibility Details (Level 4)" CREATE
>PROCEDURE,
>> >I'm sorry but I can't help you there, I haven't tried any of this
myself.
>> >Maybe Neil Pike or one of the other MVPs can help you there.

>> >--
>> >Mike MacGregor
>> >Principal Consultant
>> >TygerCon Inc.
>> >Tel: 519-579-5276
>> >Fax: 519-579-7360
>> >Cell: 416-568-4805

>> >www.tygercon.com



>> >> I don't want to use insert because the <dest> table should be
recreated
>> >from
>> >> the scratch each time I am executing select statements.
>> >> It's true that I can use different <dest> names for each select, but I
>> >want
>> >> to save space by using the same <dest > table

>> >> Related to the "Backward Compatibility Details (Level 4)" CREATE
>> PROCEDURE
>> >> (Level 4) paragraph is stated that in pre-release version:"CREATE
>> >PROCEDURE
>> >> statements failed if they contained a CREATE TABLE or SELECT INTO
>> >statement
>> >> creating a temporary table with the same name as a temporary table
that
>> >> existed at the time the CREATE PROCEDURE statement was executed."

>> >> ... and in final version:"The CREATE PROCEDURE statement succeeds."

>> >> ... but for me it doesn't work ( i have also service pack 1
installed).


>> >> >First off, SELECT INTO will create the table you are SELECTing into
>and
>> >> >cannot be executed a second time against the same table. Use INSERT
>> ><dest>
>> >> >SELECT from <a> instead after the first SELECT INTO.

>> >> >You cannot put GO within a Stored Procedure: GO is a batch command
>> >> >terminator and is not strictly a SQL command at all but is understood
>by
>> >> >software such as ISQL, ISQLW, Query Analyzer, etc..

>> >> >>> The story ended with the "Backward Compatibility Details (Level
4)"
>> >> issue
>> >> >> from BOL which drive me crazy because it doesn't work what they are
>> >> saying
>> >> >> !!!.<

>> >> >Sorry but I don't understand the question for the last part! Can you
>> >> provide
>> >> >more details?

>> >> >--
>> >> >Mike MacGregor
>> >> >Principal Consultant
>> >> >TygerCon Inc.
>> >> >Tel: 519-579-5276
>> >> >Fax: 519-579-7360
>> >> >Cell: 416-568-4805

>> >> >www.tygercon.com



>> >> >> Sorry for possible misunderstandings.

>> >> >> Please review the subjects (all of them are related):
>> >> >> 1. Select into
>> >> >> 2. GO and Stored Procedure
>> >> >> 3. Backward Compatibility Details (Level 4) drive me crazy

>> >> >> The "Select into" story started when:

>> >> >> I was trying to execute the below statements:

>> >> >> I have tried to select several times from different tables into the
>> >same
>> >> >> table (without dropping it every time).

>> >> >> i.e.

>> >> >> Select ... into <dest> from <a>
>> >> >> [ I don't want to issue drop table <dest>]
>> >> >> Select ... into <dest> from <b>
>> >> >> [ I don't want to issue drop table <dest>]
>> >> >> Select ... into <dest> from <c>
>> >> >> [ I don't want to issue drop table <dest>]
>> >> >> Select ... into <dest> from <d>
>> >> >> [ I don't want to issue drop table <dest>]

>> >> >> Note:
>> >> >> All those statements should be in the same batch. <dest> should be
>the
>> >> >same
>> >> >> table.
>> >> >> It is any solution ?.

>> >> >> The "GO and Stored Procedure" story continued when:
>> >> >> I was trying to execute the above lines in a Stored procedure. It
>> >doesn't
>> >> >> even parse and generate an error like "There is already on object
>> named
>> >> >> <dest> in the database".
>> >> >> I mention that even if I have used "drop table" before the next
>> "select
>> >> >> into" statement didn't work.
>> >> >> Any idea, solution ?.

>> >> >> The story ended with the "Backward Compatibility Details (Level 4)"
>> >issue
>> >> >> from BOL which drive me crazy because it doesn't work what they are
>> >> saying
>> >> >> !!!.

>> >> >> I appreciate any help.

>> >> >> --
>> >> >> All the best,

>> >> >> Daniel Gavrilescu

 
 
 

3 subjects for the same story !!!

Post by Rainer Flentg » Thu, 24 Jun 1999 04:00:00


Try to use single Transactions like this one:

Select ... into <dest> from <a>
go
drop table <dest>
go
Select ... into <dest> from <b>
go


> Dan,

> >To be precise from the "Backward Compatibility Details (Level 4)" issue
> >from BOL I was refering to CREATE PROCEDURE (Level 4) link.
> >This is states that I cannot execute in the same SP the next sequence:

> >Select ... into <dest> from <a>
> >drop table <dest>
> >Select ... into <dest> from <b>

> >It's generates a parse error like " the object<dest> allready exist.

> >Is there any way to avoid this ?.

> Is there any reason why all the temp tables have to have the same
> name?  Could you get along with:

> Select ... into <dest1> from <a>
> drop table <dest1>
> Select ... into <dest2> from <b>

> Roy

 
 
 

1. float precision - ALMOST the old same story !

Hi,

I'm programming SQL server via OLE-DB using Visual C++ 6.0 and MFC and
OLE-DB interfaces.
I have a double variable (for example 432404.00000000), with the follow
binary rappresentation:

F8 FF FF FF 4F 64 1A 41

So I preapre the query in order to update the column (the column is a
double).

UPDATE ivap01 SET import = 432404.00000000 WHERE etc. etc. etc.

Well, if I reload this row I obtain the double value with the follow
rappresentation:

F7 FF FF FF 4F 64 1A 41

The two binary rappresentation are different......
I know...I know....the float and real column have limited precision.
I suppose SQL server re-convert the value 432404.00000000 into float value
that could have different inner rappresentation from mine.

Well my question is:

is there a way to tell SQL server to use, in the UPDATE query, the binary
rappresentation of the double ?
So I would like to avoide its re-convertion from string to double.

Thank you.
Porlock.

2. VIFRED and computed items

3. request for database bug 'war stories'

4. VB4 and Btrieve vs. Scalable SQL questions...help me spock............

5. SQL 6.5 SP5a Horror Stories...?

6. pgaccess error

7. got any xp_sendmail stories?

8. Strange error in server ERRORLOG ...

9. Data Mining Stories

10. two stories

11. SP1 horror stories...

12. seeking successful stories for implementing data warehouse on SQL server for banking

13. User Defined Functions, and other stories....