How to use select,alter,insert into select statements one after another in the same batch?

• Bagath

  SSCrazy

  Points: 2409

  More actions

  August 24, 2006 at 11:49 pm

  #153151

  can we use select,alter,insert statements one after another....

  For ex:

  Create table ex(a int,b int)

  go

  Insert into ex values(1,2)

  go

  declare @a int

  declare @b-2 int

  declare @C int

  set @C=9

  select @a=a,@b=b from ex

  alter table ex add c int

  truncate table ex

  insert into ex select @a,@b,@c

   

  The above one is not working and giving an error

  but the below is working:

  alter table ex add c int

  go

  declare @a int

  declare @b-2 int

  declare @C int

  set @C=9

  select @a=a,@b=b from ex

  truncate table ex

  insert into ex select @a,@b,@c

  Now  i have to use the above criteria in the middle of the procedure....

  Can any one give me an idea...and one more thing is table will be create in the procedure it self only.Not out side.after create i have to take some values into variables,alter table table and insert into table.

  Any suggestion would be helpful to me....:-)

  Thanks,

  Rao Aregaddan

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  August 25, 2006 at 1:35 am

  #656776

  GO is an execute command for T-SQL

  after a GO, all variables are lost. Just as happens when you execute different queries in QA.

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• Bagath

  SSCrazy

  Points: 2409

  More actions

  August 25, 2006 at 2:41 am

  #656781

  But how to get the criteria with in that procedure....?

  is there any other way...to do like that?

  Thanks,

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  August 25, 2006 at 3:09 am

  #656783

  'GO' is the token to terminate the batch, so there is no way that you can have 'GO' inside a procedure.

  When you complie the proc, the 'GO' will end the proc, and code after that will not get compiled as part of that proc.

  Could you expand a bit on your real problem? What is it you need to do? Do you really need a 'GO' in the middle?

  If you can provide some example and description about your problem, there may be other options.

  /Kenneth

• Bagath

  SSCrazy

  Points: 2409

  More actions

  August 25, 2006 at 3:22 am

  #656789

  I dont want to use GO and know that all variables expire after that....i have given that code for ex only....

  In my proc first one temp table will be create,have to alter that table(Need to add column) and after that insert some values using the variables.

  using linked server i am creating and inserting values...

  The actual action i want is as below......

  declare @C int

  set @C=7

  select @a=a @b-2=b from temp

  alter table  temp add c

  truncate table temp

  insert into temp select @a,@b,@c

  Did u get what my plan is?

   

  Thanks,

   

   

   

   

   

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  August 28, 2006 at 2:32 am

  #657027

  You may have to adjust or rethink your processing, since what you're trying to do won't work inside the same batch.

  -- snip from BOL paragraph Batches ---

  A table cannot be altered and then the new columns referenced in the same batch.

  -- end snip --

  Is there any reason that the added column can't be there to start with, so you don't have to add it in the middle of the work?

  /Kenneth

• Bagath

  SSCrazy

  Points: 2409

  More actions

  August 28, 2006 at 2:58 am

  #657032

  Ok...Thanks for ur information.....

  I will use another temp table to get the data....

  Thanks to all............

   

  Rao Aregaddan.