I have a SP that removes the foreign keys, then truncates some files and
then adds back the keys.

Why do this?

For example:

**********************************************************************************
ALTER TABLE dbo.SalesFact DROP CONSTRAINT FK_SalesCustomer
ALTER TABLE dbo.SalesFact DROP CONSTRAINT FK_SalesProduct
ALTER TABLE dbo.SalesFact DROP CONSTRAINT FK_SalesTime
GO
TRUNCATE TABLE dbo.SalesFact
TRUNCATE TABLE dbo.SalesStage
TRUNCATE TABLE dbo.CustomerDim
TRUNCATE TABLE dbo.ProductDim
GO
ALTER TABLE dbo.SalesFact ADD
CONSTRAINT FK_SalesCustomer FOREIGN KEY
(CustomerKey) REFERENCES dbo.CustomerDim (CustomerKey)
,CONSTRAINT FK_SalesProduct FOREIGN KEY
(ProductKey) REFERENCES dbo.ProductDim (ProductKey)
,CONSTRAINT FK_SalesTime FOREIGN KEY
(TimeKey) REFERENCES dbo.TimeDim (TimeKey)


*********************************************************************************

Also, if I am going to do a large amount of inserts (say about 500,000
or more), would it be better to do the inserts first and then put the
keys back at that time?

Thanks,

Tom.

You need to drop the Foreing Key (FK) constraints because otherwise the constraint will keep you from deleting data from the table

You will get the following message
Server: Msg 4712, Level 16, State 1, Line
Cannot truncate table 'TABLE' because it is being referenced by a FOREIGN KEY constraint

The only way you could get around this is by truncating (or deleting data from) the tables in exactly the right order so as not to violate the referential integrity. For example, if table A has a FK constraint referencing tbl B and table B has a FK constraint referencing C, then you would need to truncate A then B then C. You would need to also load your data in the reverse order.

The above will not always work though because sometimes you can have a cyclical dependency in which case you have to either drop or disable your FK constraints in order to delete your data. You can disable your constraints by issuing an ALTER TABLE command (see ALTER TABLE....NOCHECK in BOL). Keep in mind that when you disable an FK constraint, you will only be able to use DELETE and not TRUNCATE

Truncate cannot check database integrity (and foreign keys) because of that
it you to drop them beforehand to be absolutelly sure what you do.
You can always use "delete from table1" and it probably will be not so
slow..

Yes, it is better to insert your data before adding foreign keys and
_indexes_.

Bojidar Alexandrov