0

I have multiple temporary tables stored in the tempdb schema for an SSIS pipeline that runs daily. The pipeline extracts data from multiple tables and stores it in the temp tables, which the data of the temp tables is later used to store in a different database.

My question is about the performance of the creation and deletion of the temp table, I want to know which approach is more optimal. Which of the following approaches is the better option? And what are the disadvantages and advantages of each one?

1- CREATE temp table if it doesn't already exist --> TRUNCATE temp table from previous data before inserting new data --> INSERT the new data into temp table.

2- DROP temp table if it exists --> use the statement SELECT INTO to insert data directly without creating a temp table in a separate statement.

edit: the tables in the tempdb are created like this CREATE TABLE tempdb..Table1

Zaid Allawanseh
  • 3
  • 2

1 Answers1

0

In my experience DROP + SELECT INTO gives better performance than CREATE + TRUNCATE + INSERT.

A disadvantage of #2 is that Truncate requires Table locks;

Rohit Gupta
  • 2,116
  • 8
  • 19
  • 25