Doing what: Loading data from many flat table CSV files into their final destination, a "Target" table.
Using: TSQL / SQL Server 2008
Goals: Don't include "duplicate" rows What are duplicates in this context? Two scenarios:
- For some tables, if two rows have the same value in all columns. ex "*"
- For other tables, if two rows have the same value in some columns ex. "COL_A, COL_B, COL_C"
I know which tables fall into which scenario, and in scenario 2, which columns are the some.
Current Strategy:
- Load "raw" data in batches (i.e. 1-5.csv, 5-10.csv, etc) into separate "Staging" database
- Dynamically create a TSQL script that transfers from
staging to target removing duplicates both...
- Within the staged data itself
- And between the staged data, and the target database where it's being transferred
- Execute the transfer TSQL script (in batches corresponding to each Staging Database) to move to a single Target database
For each of the two scenarios (i.e. whether a Table's duplicates is based on all columns (1) or some columns (2)), I have a different transfer query. Here is a pattern of each, respectively:
INSERT INTO TARGET.TABLE1 SELECT * FROM STAGING.TABLE1 EXCEPT SELECT * FROM TARGET.TABLE1
INSERT INTO TARGET.TABLE2 SELECT * FROM ( SELECT STAGING.*, ROW_NUMBER() OVER (PARTITION BY STAGING.COL_A, STAGING.COL_B, STAGING.COL_C) AS ROW_NUMBER FROM STAGING.TABLE2 as STAGING LEFT OUTER JOIN TARGET.TABLE2 TARGET ON ISNULL(STAGING.COL_A, TARGET.COL_A) IS NULL OR STAGING.COL_A = TARGET.COL_A {... repeat for COL_B and COL_C } WHERE TARGET.COL_A IS NULL) as T WHERE ROW_NUMBER = 1
I can add unique constraints if that would help.
My questions:
- Is there anything wrong with overall approach of using staging databases to store "raw data", and transferring batches of that stage into a target, eliminating duplicates within stage and between stage+target during the transfer.
I was thinking it might be more efficient to just load all raw data into its final destination, and "de-duplicate" "in-place" (though I don't know what this looks like)
- If the general approach is sound, are the above queries OK? Is "EXISTS" a safe clause for scenario 1? Is the ROW_NUMBER PARTITION BY and JOIN BY the some columns a fast way to avoid transferring duplicates in scenario 2?