CODEWITHSUNDEEP
sqlsql-servertruncate
user11703506
user11703506

Reputation:

Clean/truncate table before selecting into it

Just had a quick question to know if this is the right way to do something.

I have a query that I want to create a table. I thought about updating the table with only the changed rows, but since my query only takes about a minute, I think it is easier to just drop the whole table and rerun the query every time I do my hourly update.

Will this be the way to do it?

Truncate Table

Select *
Into Table
From TableTwo
Where X

And then just take that query, turn it into a stored procedure, and turn the procedure into a job that runs once an hour.

Also, I want this table to have indexes. Will they be preserved even if I truncate every time.

Upvotes: 1

Views: 4584

Answers (2)

Kelevra
Kelevra

Reputation: 126

Truncate doesn't drop the Table from the database, it just cleans up the table. So you won't be able to run SELECT INTO because ObjectID already exists. However Truncate preserves all the indexes and keys and table integrity

However if you drop the table you get rid it's ObjectID and then you can run SELECT INTO. It's only a good idea if the Table you're inserting from is going to have a lot of changes all the time(which is a bad thing on its own). This method doesn't preserve any indexes or keys and you'd have to create them in the Stored proc every time you run it. Which is again a bad thing on it's own.

My Suggestion is you should turn it into the Insert Into stored procedure with Truncate in it. If your company decides to make changes to the Table then you go and change your MyTable and SP, it's more headache, but usually companies don't change their database structure very often, unless the database is still in a Development or Testing and not live. In that case SELECT INTO will be only a temporary solution.

CREATE PROC MyProc
as

TRUNCATE TABLE MyTable;

INSERT INTO MyTable (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM TableTwo

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can do this. I would probably advise dropping the table instead. This will better handle changes in table structure.

If you do use truncate, you want insert rather than into:

insert into Table  -- column list is recommended
    Select *
    From TableTwo
    Where X;

Dropping the table might take an iota more time, and it doesn't preserve triggers, constraints, foreign key references, and storage definitions. (I'm guessing those are not important.) However, it does allow the query to change over time, which might be useful to future-proof the code.

Upvotes: 2

Related Questions