How to use Staging Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

How to use Staging Table

Author

Topic

Avdhut
Starting Member

11 Posts

Posted - 2012-03-05 : 01:00:55

Hi,
I am Using SSIS Package.In that I has Execute SQL Task to Update the Table.It Takes time,recently I read about use of staging table.
But I don't know the concept of staging data also how to use it.

I will be thankful for the help.

Kristen
Test

22859 Posts

Posted - 2012-03-05 : 09:15:03

Here's my definition:

I create staging tables to import data from "other sources" where that data may need sanitizing, converting, or otherwise "mangling" in some way.

I usually create the columns as much bigger than the longest data they are likely to receive so that nothing ever gets "clipped"

I only use a specific datatype, such as INTEGER or DATE, if I am certain that the format the data will arrive in is a) unambiguous and b) will convert 100% to the datatype I have choosen - e.g. for dates that they are yyyymmdd rather than containing hyphens and mm-dd-yyyy or any other variant where SQL Server may choose a conversion method, ambiguously, based on what it thinks is right.

I add some columns to the table - usually "ErrorNo" and "ErrorMsg". I then do a series of updates on the staging table to sanitise the data, or for validation.

I add columns to the staging table to store any "equivalent" IDs of associated data in the target database (after matching up on Customer Name, or whatever)

For example:


UPDATE S
SET S.ErrorNo = CASE WHEN C.CustomerName IS NULL THEN 1 END,
    S.ErrorMsg = CASE WHEN C.CustomerName IS NULL THEN 'Customer not found' END,
    S.NewCustomerID = C.CustomerID
FROM MyStagingTable AS S
     LEFT OUTER JOIN MyCustomerTable AS C
          ON C.CustomerName = S.SomeCustomerNameColumn
WHERE     ErrorNo IS NULL -- Only process rows that have no previous validation errors

then only rows where ErrorNo IS NULL are "clean" can be imported (or, maybe, ALL rows have to be considered "Clean" before ANY can be imported)

UPDATE statements can be used to update the data in the staging table, or users can manually update it if necessary.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources