Creating and Managing SQL Scripts - Microsoft Business Intelligence Tools for Excel Analysts - page 202

Load an empty table. You might perform a full data load into a target table, as shown in Listing 10-5.
That could occur upon first load or a reload. In those cases, you need to follow a specific scripting
pattern to make sure you're performing an efficient load. It involves truncating the table, dropping
any indexes, inserting new data, and reloading the indexes.
Listing 10-5: Fulling Loading Data
Truncate Table Deliver.Customer
--Check if index exists
If Exists (Select *
From sys.indexes
Where Object_id = Object_Id(N'Deliver.Customer')
And name = N'Indexname')
--Drop index
Drop Index IndexName on Deliver.Customer
Insert Into Deliver.Customer
Select s.CustomerKey,
s.CustomerFirstName,
s.CustomerLastName,
s.CustomerEmail,
LoadDate=GetDate(),
ModifyDate=GetDate()
From Prepare.Customer s
--Reload indexes
--Check if index exists
If Exists (Select *
From sys.indexes
Where Object_id = Object_Id(N'Deliver.Customer')
And name = N'Indexname')
--Create index
Create clustered Index IndexName on Deliver.Customer
(
CustomerKey
)
Error handling
Error handling is an important part of your system. Without this function you may end up with bad
data loaded into your target tables. There are two primary reasons for error handling:
➤ Trapping unexpected errors when they occur and raising an alert to users
➤ Handling errors raised purposefully in case specified conditions occur
The primary mechanism for handling errors in SQL Server is using the Try and Catch syntax, as shown
in Listing 10-6.