Your transaction log might be building up a lot...
you can insert into the table in batches...
The result of such inserts is a batch might fail but the previous batches will be commited to the database..

Sorry for the delay, but work been a bitch. It is a weekly job, but the first time this runs will be a very large pull of data. I like the SSIS approach, but how can I commit or rollback in SSIS of if the package fails. This is critical data and I need to be sure that the records are loaded or if package fails the job is roll backed.

WB

0

wdbatesAuthor Commented: 2013-06-06

Both answers were helpful and I will be able to use each in difference situations.