Query Analyzer syntax checker prevents valid statements from executing

[Guest_imported]

The syntax checker in Query analyzer is preventing me from performing a series of steps, when the second step relies on the results of the first step. Example:

select * into step1_out from step1_in where [condition]
select keyfield, count(*) into step2_out from step1_out

Query analyzer will give me an error saying that object step1_out does not exist.

This doesn't work either:

begin transaction
select * into step1_out from step1_in where [condition]
drop table step1_in
committ transaction
begin transaction
sp_rename step1_out, step1_in
committ transaction
begin transaction
select keyfield, count(*) into step2_out from step1_in
drop table step1_in
committ transaction
begin transaction
sp_rename step1_out, step1_in
committ transaction

Query analyzer gives me an error with sp_rename.

My immediate problem is that I need to add collumns to a table through a series of joins, dropping the input table and renaming the output table after each step. Since these are big tables I would like to automate this in a batch. How can I do this?

 

[JamesJKusch]

Try using an "end" after your commit. Thanks for all the help ... As Usual

J. Kusch
Chameleon Solutions, Inc.

 

[tlbroadbent]

You have a couple of syntax errors in the snippet. Try this query.

select * into step1_out from step1_in where [condition]

select keyfield, count(*) As RecCnt
into step2_out from step1_out
Group By keyfield
Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[brownbear]

One way around your problem is to use the EXECUTE command to wrap the statements:

EXECUTE('select * into step1_out from step1_in where [condition]')
EXECUTE('select keyfield, count(*) into step2_out from step1_out')

The statements wrapped by EXECUTE are not checked by the syntax checker but are parsed and executed at runtime.

I have used this method to overcome similar problems.

One caveat here if you are using a SQL Server version prior to SQL Server 2000.

If you are setting your database to allow BULK COPY/SELECT INTO (necessary to run the query for permanent tables before SQL Server 2000), this means that you cannot recover your database from your transaction logs.