I have a problem in an ETL process. I need to integrate data into a data warehouse. The data is associated with a "variant". A variant is defined as having a specific set of parameters (identified by a property bag table with name and value, both varchar).
Variant 1 Field A value 11 Field B Value 22 Variant 2 Field A value 12 Field B value 23
A simplified table structure:
CREATE TABLE [simstg].[Parameter]( [VariantNo] [smallint] NOT NULL, [Variable] [varchar](64) NOT NULL, [Value] [float] NOT NULL, CONSTRAINT [PK_Parameter] PRIMARY KEY CLUSTERED ( [VariantNo] ASC, [Variable] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) )
I need to import those variants (and the number can not be changed) from staging tables into a final resting table. THere we do have similar Variant Numbers, but the numbers DO NOT MATCH UP. So I need to map the variants. Variant 1 from the staging tables may be variant 22 in the final tables, or may not exist (in which case I create a new variant number).
I am stuck trying to identify the variants, efficiently. I know I am looking for a variant number in the target table that has the same number of parameters, with every parameter having the exact same value. You can assume the table s are identical - I can take it from there.
I know I can do that with a cursor - but going through every variant in the target table is prohibitive expensive. I know I can also generate a key string on the client side (or via a SP) and / or put a hashcode on that one and store them (which could happen SQL side). I could use dynamic SQL to basically generate a parameter string, then use HasHBytes on top of it to get a hash - then compare hashes to rule out most of the data.
But is there a more efficient way?