Results 1 to 3 of 3

Thread: Inserts....

  1. #1
    Join Date
    Jan 2003
    Posts
    14

    Post Unanswered: Inserts....

    Hi..
    I have a problem...
    I have a temporary table and I'm traying to do this...
    create table #tmp
    ( valor varchar(100),
    valor1 varchar(100),
    valor2 varchar(100)
    )

    declare @cmd varchar(500)

    select @cmd = 'Select valor, valor2 from mytable)

    insert into #tmp(valor, valor2)
    exec(@cmd)

    select valor, valor2 from #tmp

    but I have an error... 'Invalid object name #tmp'

    why??

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    other than a few typos everything looked good try this:

    Code:
    if object_id('mytable') is not null drop table mytable
    if object_id('tempdb..#tmp') is not null drop table mytable
    
    create table mytable(
     valor varchar(100),
     valor1 varchar(100),
     valor2 varchar(100))
    
    insert into mytable values('A','B','C')
    insert into mytable values('D','E','F')
    insert into mytable values('G','H','I')
    insert into mytable values('J','K','L')
    
    create table #tmp(
     valor varchar(100),
     valor1 varchar(100),
     valor2 varchar(100))
    
    declare @cmd varchar(500)
    
    select @cmd = 'Select valor, valor2 from mytable'
    
    insert into #tmp(valor, valor2)
    exec(@cmd)
    
    select valor, valor2 from #tmp
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: Inserts....

    If you have this code inside of a stored proc it should work, otherwise it won't because your temporary table is local not global. (local temporary tables are visible only in the current sesion).
    Try using:
    CREATE TABLE ##tmp

    or

    CREATE TABLE tempdb..tmp

    see "tempoary tables" in BOL for more details
    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •