Professional Web Applications Themes

Temporary Table Query - Microsoft SQL / MS SQL Server

Hi, I am inserting data into a temporary table using the following method: select a into #b from c This method works fine, but...if i want to choose what is inserted into temporary table #b, like in this sproc: if choice = 'A' begin select a into #b from c where d = 10 end else begin select a into #b from c where d = 20 end I get an error message telling me that there is an object already called #b, even though each select..into is mutually exclusive? Is there a way to get round this problem. Thanks ...

  1. #1

    Default Temporary Table Query

    Hi,

    I am inserting data into a temporary table using the
    following method:

    select a
    into #b
    from c

    This method works fine, but...if i want to choose what is
    inserted into temporary table #b, like in this sproc:


    if choice = 'A'
    begin
    select a
    into #b
    from c
    where d = 10
    end
    else
    begin
    select a
    into #b
    from c
    where d = 20
    end

    I get an error message telling me that there is an object
    already called #b, even though each select..into is
    mutually exclusive?

    Is there a way to get round this problem.

    Thanks in advance

    Jon

    Jonathan Derbyshire Guest

  2. #2

    Default Re: Temporary Table Query

    You could use a UNION statement. Once the table exists you can't "select
    into" it.

    "Jonathan Derbyshire" <Jonathan.DerbyshireStudent.shu.ac.uk> wrote in
    message news:0b0f01c3423d$5aa8dc40$a501280aphx.gbl...
    > Hi,
    >
    > I am inserting data into a temporary table using the
    > following method:
    >
    > select a
    > into #b
    > from c
    >
    > This method works fine, but...if i want to choose what is
    > inserted into temporary table #b, like in this sproc:
    >
    >
    > if choice = 'A'
    > begin
    > select a
    > into #b
    > from c
    > where d = 10
    > end
    > else
    > begin
    > select a
    > into #b
    > from c
    > where d = 20
    > end
    >
    > I get an error message telling me that there is an object
    > already called #b, even though each select..into is
    > mutually exclusive?
    >
    > Is there a way to get round this problem.
    >
    > Thanks in advance
    >
    > Jon
    >

    Jason MacKenzie Guest

  3. #3

    Default Re: Temporary Table Query

    check the object existance condition at the begining of the code.
    Ex:
    if object_id('tempdb..#b') is not null
    drop table #b

    if choice = 'A'
    begin
    select a
    into #b
    from c
    where d = 10
    end
    else
    begin
    select a
    into #b
    from c
    where d = 20
    end


    --
    -Vishal
    "Jonathan Derbyshire" <Jonathan.DerbyshireStudent.shu.ac.uk> wrote in
    message news:0b0f01c3423d$5aa8dc40$a501280aphx.gbl...
    > Hi,
    >
    > I am inserting data into a temporary table using the
    > following method:
    >
    > select a
    > into #b
    > from c
    >
    > This method works fine, but...if i want to choose what is
    > inserted into temporary table #b, like in this sproc:
    >
    >
    > if choice = 'A'
    > begin
    > select a
    > into #b
    > from c
    > where d = 10
    > end
    > else
    > begin
    > select a
    > into #b
    > from c
    > where d = 20
    > end
    >
    > I get an error message telling me that there is an object
    > already called #b, even though each select..into is
    > mutually exclusive?
    >
    > Is there a way to get round this problem.
    >
    > Thanks in advance
    >
    > Jon
    >

    Vishal Parkar Guest

  4. #4

    Default Temporary Table Query

    Select into creates a new table and inserts into it.
    If you want to reuse the table there is no need for select
    into anymore. The table exist as a local temporary table,
    just insert into it. Why would you need to select into
    twice in the same table.
    The first select into did the job of creating the table.
    So the second time you want to insert into #b, just insert,
    it is there.

    Hope that helps.


    >-----Original Message-----
    >Hi,
    >
    >I am inserting data into a temporary table using the
    >following method:
    >
    >select a
    > into #b
    > from c
    >
    >This method works fine, but...if i want to choose what is
    >inserted into temporary table #b, like in this sproc:
    >
    >
    >if choice = 'A'
    >begin
    > select a
    > into #b
    > from c
    > where d = 10
    >end
    >else
    >begin
    > select a
    > into #b
    > from c
    > where d = 20
    >end
    >
    >I get an error message telling me that there is an object
    >already called #b, even though each select..into is
    >mutually exclusive?
    >
    >Is there a way to get round this problem.
    >
    >Thanks in advance
    >
    >Jon
    >
    >.
    >
    Laurent Lemire Guest

Similar Threads

  1. Replies: 8
    Last Post: January 26th, 05:01 PM
  2. Temporary Table Creation Problem in CF7 not CF6.1
    By Ortho in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 24th, 04:20 PM
  3. Get result from temporary Oracle Table
    By Franck in forum ASP.NET Web Services
    Replies: 0
    Last Post: April 28th, 07:48 AM
  4. #26387 [NEW]: TABLE TEMPORARY DOES NOT WORK
    By davefazio at annulet dot com in forum PHP Development
    Replies: 0
    Last Post: November 24th, 08:09 PM
  5. Replies: 3
    Last Post: December 6th, 02:36 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139