locked
How to generate surrogate key om combination of 2 columns RRS feed

  • Question

  • Hi ,

    I have to generate surrogate key based on combination of 2 columns.

    I have Companycode and Productcode, i need to generate surrogatekey on combination of Companycode and productcode

    kindly help me.

    Thanks

    Thursday, March 10, 2016 10:38 AM

Answers

  • Hi,

    Companycode     status       Documentnumber

    1                          parked          10000122

    1                          posted          10000122

    how to generate surrogate key ?

    In Fact table, i have only one line item for document number.


    For this data you cant create surrogate key based on companycode and documentnumber combination as its not unique

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 10, 2016 11:51 AM

All replies

  • Just make surrogate key column an identity one or based on a sequence and make sure you insert only unique combinations of CompanyCode and ProductCode in the table and then it will work fine. For checking uniqueness you can use EXISTS clause based check or add a unique constraint on the columns

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 10, 2016 10:49 AM
  • Hi,

    Companycode     status       Documentnumber

    1                          parked          10000122

    1                          posted          10000122

    how to generate surrogate key ?

    In Fact table, i have only one line item for document number.


    • Edited by DevBi Thursday, March 10, 2016 11:10 AM
    Thursday, March 10, 2016 11:08 AM
  • SELECT <cols>,ROW_NUMBER() OVER (PARTITION BY Companycode     ,Documentnumber ORDER BY Documentnumber) rn

    FROM tbl

    Is  rn column what you are looking for?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 10, 2016 11:48 AM
  • Hi,

    Companycode     status       Documentnumber

    1                          parked          10000122

    1                          posted          10000122

    how to generate surrogate key ?

    In Fact table, i have only one line item for document number.


    For this data you cant create surrogate key based on companycode and documentnumber combination as its not unique

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 10, 2016 11:51 AM