locked
dropping column from temp table RRS feed

  • Question

  • hello,

    I have a PROC that creates a temp table to collate date from other sources. as part of this, I was wondering if I could remove a column in this temp table (as its only used to get hold of other data) .

    So, table design looks like this 

    DECLARE @results TABLE
    (reservation_id		numeric(10,0)	null,
    on_date			datetime	null,
    booking_date		datetime	null,
    ci_date			datetime	null,
    co_date			datetime	null
    
    OTHER COLUMNS
    
    )


    I think try to remove a column 

    ALTER TABLE @results
    DROP COLUMN reservation_id


    but I get the error

    Incorrect syntax near '@results'.

    Now, I understand I might be doing something incorrectly / you cannot do here, I just wanted to know if I could do it ,and if so ,how.

    thanks

    Thursday, April 9, 2020 3:48 PM

Answers

  • The table variable is a local variable. Its structure cannot be changed after it has been declared. You can create a new table variable without that column and then copy data into it.

    A Fan of SSIS, SSRS and SSAS


    • Marked as answer by G-Oker Thursday, April 9, 2020 5:00 PM
    • Edited by Guoxiong Yuan Friday, April 10, 2020 7:43 PM
    Thursday, April 9, 2020 4:04 PM

All replies

  • The table variable is a local variable. Its structure cannot be changed after it has been declared. You can create a new table variable without that column and then copy data into it.

    A Fan of SSIS, SSRS and SSAS


    • Marked as answer by G-Oker Thursday, April 9, 2020 5:00 PM
    • Edited by Guoxiong Yuan Friday, April 10, 2020 7:43 PM
    Thursday, April 9, 2020 4:04 PM
  • thank you for the clarification . :-) Keep safe and well
    Thursday, April 9, 2020 5:01 PM