What technique to use to update production table from staging table

Hi I have a production table in SQL Server 2005 that has approx 500,000 records---every 6 hours this table needs to be truncated and filled

The basic SSIS package uses a script compentant and imports the data into a staging table which has the same structure as the production table. I have a final Execute SQL Task that Truncates the production table and does a Insert into production-select * from stage table.

Takes around 30 seconds to run this last Execute SQL Task--problem is there is a risk that our webservice will query this table during the Execute SQL Task and return incorrect results.

Q1: In this last Execute SQL Task if I used a BEGIN TRANSACTION and COMMIT TRANSACTION; will this be any quicker

Q2: In this last Execute SQL Task- would it be better to use a RENAME TABLE technique in TSQL--any code examples

Q3:Is there any way in TSQL I can compare EVERY FIELD in two tables ie Stage and Production which have identical data structures and figure out a way to update only the records that changed Is SQL Server Replication the best way to do this

thanks kindly

Dave

Thanks Phil,

Would you mind ziping up that SSIS project in your blog entry and emailing to me [removed e-mail address to keep spam bots away - Phil Brammer]

I have also thought of a much simpler idea --stick to idea of loading data into a stage table that mirrors production and then have a final execute SQL task that does as outlined here http://www.mssqltips.com/tip.asp tip=1023

example of using a CHECKSUM is to store the unique value for the entire row in a column for later comparison. This would be helpful in a situation where all of the rows in a table need to be compared in order to perform an UPDATE.

SET NOCOUNT ON
-- SQL Server 2005
USE ProfilerTest;
GO
UPDATE a
SET TextData = 'Not Applicable'
FROM dbo.ProfilerResults3 a
WHERE a.RowCheckSum = CHECKSUM(RowNumber, EventClass, ApplicationName);
GO

SELECT * 
FROM dbo.ProfilerResults3;
GO