Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2012
    Location
    India,Mumbai
    Posts
    34

    Unanswered: Truncate command

    Hi All,

    How can we know that, how many rows got truncated of particular table by executing Truncate statement on table.

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by suraj_pandit View Post
    Hi All,

    How can we know that, how many rows got truncated of particular table by executing Truncate statement on table.
    The simple answer: all records are deleted, obviously.

    Truncate is a non logged operation. If you decide to use truncate, apparently you need all records deleted. Truncate = truncate = all records.
    You could do a select count(*) on the table you're about to truncate so you know how many records there were.

    If you want any kind of logging or feedback on numbers of records, use the normal delete-command.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hello,
    I don't think so, the global variable @@rowcount is set to 0 in commands that don't return any value, such as truncate.

    A possible solution is to make counts, something like:

    declare @pre_value int
    select @pre_value = count(*) from [tablename]

    truncate table [tabelname]
    (clauses)...


    declare @pos_value int
    select @pos_value = count(*) from [tablename]


    select 'Changed rows ' + convert(varchar,(@pre_value - @pos_value))


    This will give you the diference of rows, as long as the table is not being inserted at the same time by some other process.

    Hope it helps.

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by Catarrunas View Post
    Hello,
    I don't think so, the global variable @@rowcount is set to 0 in commands that don't return any value, such as truncate.

    A possible solution is to make counts, something like:

    declare @pre_value int
    select @pre_value = count(*) from [tablename]

    truncate table [tabelname]
    (clauses)...


    declare @pos_value int
    select @pos_value = count(*) from [tablename]


    select 'Changed rows ' + convert(varchar,(@pre_value - @pos_value))


    This will give you the diference of rows, as long as the table is not being inserted at the same time by some other process.

    Hope it helps.
    Truncate table does not support any clauses afaik. Truncating a table remove all records, so afterwards you always end up with zero rows.
    A select count(*) from <tablename> before the truncate is therefore sufficient to check how many records are truncated.
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Yes, of course, i'm sorry for the mistake i was thinking of delete!
    Thank you for the correction.

  6. #6
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by Catarrunas View Post
    Yes, of course, i'm sorry for the mistake i was thinking of delete!
    Thank you for the correction.
    No problem
    It would be sad for the topicstarter if he wanted to truncate only certain records and suddenly finds his table completely empty...
    I'm not crazy, I'm an aeroplane!

  7. #7
    Join Date
    Feb 2012
    Location
    India,Mumbai
    Posts
    34

    Truncate command

    Quote Originally Posted by Martijnvs View Post
    The simple answer: all records are deleted, obviously.

    Truncate is a non logged operation. If you decide to use truncate, apparently you need all records deleted. Truncate = truncate = all records.
    You could do a select count(*) on the table you're about to truncate so you know how many records there were.

    If you want any kind of logging or feedback on numbers of records, use the normal delete-command.
    Thanks Martijnv for your Prompt .

    In the actual scenario we have two table title and title_count.

    Table title:
    title_id title_name

    Table title_count : numbers_of_records

    With the help of triggers On every DML(insert,Update and delete) statement, table title_count table gets updated with total numbers records/tuples of title table.
    Now, I also want that table title_count should automatically get updated with total numbers of rows of table title on truncate command. as we all know that we cannot fire trigger on Truncate command.
    How can we get effect of trigger on truncate statements ?
    Please let me know in case of any confusion.

  8. #8
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by suraj_pandit View Post
    Thanks Martijnv for your Prompt .

    In the actual scenario we have two table title and title_count.

    Table title:
    title_id title_name

    Table title_count : numbers_of_records

    With the help of triggers On every DML(insert,Update and delete) statement, table title_count table gets updated with total numbers records/tuples of title table.
    Now, I also want that table title_count should automatically get updated with total numbers of rows of table title on truncate command. as we all know that we cannot fire trigger on Truncate command.
    How can we get effect of trigger on truncate statements ?
    Please let me know in case of any confusion.
    I assume you have some kind of script or procedure that executes the truncate-command. You can put the select coun(*) in there too, and insert the result in your loggingtable.

    Can you explain why you use truncate instead of delete? And why do you want to delete/truncate all records from the title-table?

    But most importantly:
    As I mentioned, truncate is a non-logged operation and therefore does not fire a trigger. If you want to use trigger-functionality, you cannot use truncate.
    I'm not crazy, I'm an aeroplane!

Posting Permissions

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