Post reply

Truncate variable table

  • October 22, 2007 at 1:33 pm

    #180594

    Is there any way to Truncate a Variable table i.e.

    declare @t table

    (

    f1 int

    )

    insert into @t

    select someField from someTable

    --some processing

    .

    .

    truncate table @t

    --this generates an error -- I am trying to avoid using the delete if I can

    I am only doing this for better efficiency as I understand truncating a table is faster than deleting the records in a table -- all help appreciated

  • October 22, 2007 at 2:06 pm

    #744240

    you can't truncate a table variable. you could either use a temp table or just simply create a new table variable and use it and just let them both fall out of scope at the end.

  • October 22, 2007 at 2:56 pm

    #744250

    It's always good to read whole related topics in BOL and try to understand WHY.

    Truncating a table is faster than deleting the records in a table because it does uses simplest table lock and it does record transactions into log.

    None of these is used for table variables.

    So, DELETE from table variable is as good as TRUNCATE would be.

    _____________
    Code for TallyGenerator

  • December 18, 2020 at 3:33 pm

    #3822082

    I know its to late 🙂 but to help others I commented

    you can truncate using the command mentioned below

    DELETE FROM @myList

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply