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).

For example:

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,
    [VariantNo] ASC,
    [Variable] ASC

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.