CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
Alex Zhukovskiy
Alex Zhukovskiy

Reputation: 10025

Fast replace table in T-SQL with another

I have two tables with same structure (keys/columns/etc). I want to replace data in first table with data from second one. I use following code to do it:

DROP TABLE T1

SELECT *
INTO T1
FROM T2

DROP TABLE T2

but this code is slow enough when T2 is large. T2 table is temporary so I want to rewrite it as:

drop table T1

EXEC sp_rename 'T2', 'T1'

This should execute very fast for any size table, but am I missing something here? Some side effects that may break this code? I'm not very familiar with dynamic SQL so please advice.

Upvotes: 2

Views: 2366

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Renaming the tables should be fine. Sometimes, there can be issues with triggers or foreign key constraints (and the like). However, you are dropping T1 anyway, so this is not a concern.

The one issue is where the data is actually stored. If by temporary table, you mean a table name that starts with #, then this is not a good approach, because temporary tables are often stored separately from other tables. Instead, create the table in the same place where T1 would be stored, perhaps calling it something like temp_T1.

You might want to revisit your logic to see if there is a way to "reconstruct" T1 in place. However, when there are large numbers of updates and deletes in the processing, recreating the table is often the fastest approach.

Upvotes: 3

Related Questions