select from table variable

• sanya.ibrahim

  SSC Veteran

  Points: 286

  More actions

  November 22, 2009 at 8:47 pm

  #156426

  Hi,

  Steps in my sp goes like this:-

  declare @mytable table (a int, b int, c int);

  insert into @mytable select tablename.field1, tablename.field2, tablename.field3;

  declare @newtable table (d int, e int, fint);

  insert into @newtable select @mytable.a, @mytable.b, @mytable.c;

  in the last line, i get an error message that " must declare scalar variable @mytable".

  What do I do?

  Thanks.

  Sanya

• Bru Medishetty

  SSC-Insane

  Points: 23606

  More actions

  November 22, 2009 at 9:27 pm

  #1082797

  sanya.ibrahim (11/22/2009)

  ---

  declare @mytable table (a int, b int, c int);

  insert into @mytable select tablename.field1, tablename.field2, tablename.field3;

  You don't have from clause in the above statement how will it work?

  You need to change the query

  Insert into @mytable select field1, field2, field3 from tablename

  declare @newtable table (d int, e int, fint);

  insert into @newtable select @mytable.a, @mytable.b, @mytable.c;

  You need to change the query

  Insert into @newtable select a, b, c from @mytable

  ---

  Bru Medishetty

  Blog -- LearnSQLWithBru

  Join on Facebook Page Facebook.comLearnSQLWithBru

  Twitter -- BruMedishetty

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  November 23, 2009 at 7:04 am

  #1082920

  Also, you cannot use a table variable name as a qualifier. You must alias the table variable as shown below. This will become important when you use table variables joined to other tables.

  declare @sample Table (ID int identity(1,1) primary key, Name varchar(50))

  insert into @sample

  select 'Adam' union all

  select 'Brad' union all

  select 'Carla'

  select S.ID, S.Name from @sample S -- you can't use @sample.ID and @sample.Name

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

• sanya.ibrahim

  SSC Veteran

  Points: 286

  More actions

  November 23, 2009 at 8:31 am

  #1082970

  Bru Medishetty (11/22/2009)

  ---

  sanya.ibrahim (11/22/2009)

  ---

  declare @mytable table (a int, b int, c int);

  insert into @mytable select tablename.field1, tablename.field2, tablename.field3;

  You don't have from clause in the above statement how will it work?

  You need to change the query

  Insert into @mytable select field1, field2, field3 from tablename

  declare @newtable table (d int, e int, fint);

  insert into @newtable select @mytable.a, @mytable.b, @mytable.c;

  You need to change the query

  Insert into @newtable select a, b, c from @mytable

  Sorry, I forgot to write the "from" clause in the code I wrote here in the forum. The actual sp has the 'from' clause.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 23, 2009 at 8:34 am

  #1082971

  sanya.ibrahim (11/23/2009)

  ---

  Sorry, I forgot to write the "from" clause in the code I wrote here in the forum. The actual sp has the 'from' clause.

  Then post the actual sp.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Bru Medishetty

  SSC-Insane

  Points: 23606

  More actions

  November 23, 2009 at 8:55 am

  #1082991

  GilaMonster (11/23/2009)

  ---

  sanya.ibrahim (11/23/2009)

  ---

  Sorry, I forgot to write the "from" clause in the code I wrote here in the forum. The actual sp has the 'from' clause.

  Then post the actual sp.

  Best possible suggestion !

  ---

  Bru Medishetty

  Blog -- LearnSQLWithBru

  Join on Facebook Page Facebook.comLearnSQLWithBru

  Twitter -- BruMedishetty

• sanya.ibrahim

  SSC Veteran

  Points: 286

  More actions

  November 23, 2009 at 9:36 am

  #1083014

  Sorry guys for all the trouble, but the suggestion by SSCrazy resolved the issue.

  Thanks a lot SSCrazy and all others

• Bru Medishetty

  SSC-Insane

  Points: 23606

  More actions

  November 23, 2009 at 9:41 am

  #1083016

  sanya.ibrahim (11/23/2009)

  ---

  Sorry guys for all the trouble, but the suggestion by SSCrazy resolved the issue.

  Thanks a lot SSCrazy and all others

  One more suggestion..

  Bob Hovious 24601 is the user not SSCCrazy

  ---

  Bru Medishetty

  Blog -- LearnSQLWithBru

  Join on Facebook Page Facebook.comLearnSQLWithBru

  Twitter -- BruMedishetty

• sanya.ibrahim

  SSC Veteran

  Points: 286

  More actions

  November 24, 2009 at 9:41 am

  #1083541

  So sorry......

  (: OK, you guys can have one more beer on me.....

  and thanks once again

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  November 24, 2009 at 10:09 am

  #1083561

  You're welcome... and don't worry.

  I've been called lots worse things than SSC Crazy.

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills