Truncate variable table

  • 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

  • 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.

  • 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

  • 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