behavioral difference between DELETE and TRUNCATE

  • Hello all,

    I have an issue with Delete statement.

    In the code given below (its a part of actual proc),

    if we use TRUNCATE to clean the temp tables, everything goes fine.

    But if I use DELETE in place of truncate, system skips the IF loop 'if (@script_type = 1 OR @script_type = 2)'

    I am not able to understand this behavioral difference between DELETE and TRUNCATE.

    Recently the database is being used for replication, but that should not be a reason.

    SELECT @max_rows = COUNT('X') FROM #temp_table1

    SET@row_cnt = 1

    WHILE @row_cnt <= @max_rows

    BEGIN

    select @field1 = field1,@field2 = field2,@field3 = field3 from#temp_table1

    whererow_num = @row_cnt

    insert into #temp_table3(.....) select .....

    SELECT @max_copy_rows = COUNT('X') FROM #temp_table3

    WHILE @copy_row_cnt <= @max_copy_rows

    BEGIN

    select@copy_field1 = field1 from#temp_table3 whererow_num = @copy_row_cnt

    if (@script_type = 1 OR @script_type = 2)

    begin

    set @sql = .....

    end

    else

    begin

    set @sql = .....

    end

    truncate table dbo.#temp_table2

    set @column_sql = .....

    exec(@column_sql)

    set @copy_row_cnt = @copy_row_cnt + 1

    END

    SET @row_cnt = @row_cnt + 1

    truncate table #temp_table3

    END

  • You need to supply a complete script that will display your problem.

    I've changed the code you provided so it will run without initial errors, but this code does NOT have the problem you describe. In both cases (using the DELETE and using the TRUNCATE) the IF block will be executed.

    declare @max_rows int, @max_copy_rows int

    declare @row_cnt int, @copy_row_cnt int

    declare @field1 int, @field2 char(1), @field3 char(1)

    declare @copy_field1 int, @copy_field2 char(1), @copy_field3 char(1)

    declare @script_type int

    declare @sql nvarchar(500), @column_sql nvarchar(500)

    create table #temp_table1 (field1 int, field2 char(1), field3 char(1), row_num int identity(1,1))

    create table #temp_table2 (field1 int, field2 char(1), field3 char(1), row_num int)

    create table #temp_table3 (field1 int, field2 char(1), field3 char(1), row_num int)

    insert into #temp_table1 (field1, field2, field3) values(1, 'a', 'a'), (3, 'c', 'c'), (2, 'b', 'b')

    SELECT @max_rows = COUNT('X') FROM #temp_table1

    SET @row_cnt = 1

    SET @copy_row_cnt = 1

    WHILE @row_cnt <= @max_rows

    BEGIN

    select @field1 = field1,@field2 = field2,@field3 = field3 from #temp_table1

    where row_num = @row_cnt

    insert into #temp_table3

    select * from #temp_table1

    SELECT @max_copy_rows = COUNT('X') FROM #temp_table3

    WHILE @copy_row_cnt <= @max_copy_rows

    BEGIN

    print 'x'

    select @copy_field1 = field1 from #temp_table3 where row_num = @copy_row_cnt

    if (@script_type = 1 OR @script_type = 2)

    begin

    set @sql = 'type 1 or 2'

    print @sql

    end

    else

    begin

    set @sql = 'other type'

    print @sql

    end

    truncate table dbo.#temp_table2

    --delete from #temp_table2

    set @column_sql = 'select * from #temp_table2'

    exec(@column_sql)

    set @copy_row_cnt = @copy_row_cnt + 1

    END

    SET @row_cnt = @row_cnt + 1

    truncate table #temp_table3

    --delete from #temp_table3

    END

    drop table #temp_table1

    drop table #temp_table2

    drop table #temp_table3

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Why all the looping and dynamic sql to create inserts? From what you posted this looks procedural instead of set based. If you want some help to make this whole process better post some more details and I bet this can be made a lot more efficient.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

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