How to make a staging table in SQL Server 2005?

Question

Hi

I heard about doing staging tables and I am wondering how you actually do it.

I want to do a SqlBulkCopy into a staging table. Then then update the real table.

I heard in SQL Server 2008 that you can use something called merge but I am using SQL Server 2005 so I heard I have to use a update inner join?

I am not sure how that would look like(I am guessing best way would be to write it in SP). Also the staging table needs to be able to handle concurrent updates.

I was thinking of putting another column in the staged as GUID so that way I know which records belong to which group and when it is time to delete the records from the staged table I can just use that.

So can anyone show me any examples or tutorials on how to do it? Also how fast is this way? Say if your updating 50,000 records how long would that take(a guesstimate is fine)

Edit

So this is my SP now. I think it should be able to do concurrent connections but I wanted to make sure.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_MassUpdate]
@BatchNumber uniqueidentifier 
AS
BEGIN
    update Product
    set ProductQty = 50
    from Product prod
    join StagingTbl stage on prod.ProductId = stage.ProductId
    where stage.BatchNumber = @BatchNumber

    DELETE FROM StagingTbl
    WHERE BatchNumber = @BatchNumber

END

Answer 1

You create a staging table the same way you create a regular table. With a CREATE TABLE script that defines the columns and datatypes.

OK

update prod
set field1 = stage.field1
from myproductiontable prod
join mystagingtable stage on prod.somefield = stage.somefield

To add records not in prod

Insert myproductiontable (f1, f2, f3)
select stage.f1, stage.f2, stage.f3 
from mystagingtable stage
where  not exists (select someid from myproductiontable prod WHERE stage.somefield = prod.somefield)

But you have to have one or more fields in the staging table that relate to the records in the prod table. Sometimes you have to do an additional join to get that relationship.