Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2013
    Posts
    2

    Unanswered: How to Import CSV data into SQL Table with Foreign Key Conversion

    Our SQL 2008 R2 relational database has tables with foreign key relationships for part numbers. We receive production data from a separate program and we need to import the CSV data into our database application.

    The problem is our separate program creates a CSV file with the actual part number "362S162-33". In our database we have a separate parts table (example: 362S162-33 has identity "15").

    We need to import data into a production table that has a "part number" (FK) column.

    How can we, when importing, cross-reference the "parts table" to convert the part number to the identity number. We have thousands of parts, so we need this change of part number column to the FK identity automatically on import.

    Production Table:
    idComponent (PK), [1000]
    ComponentName, [Assembly108]
    idPartNumber (FK), [15]
    ComponentLength, [230.5]
    UserMessage, [Assembly is 230.5 inches using 362S162-33]
    Qty; [1]

    Thank you for your help and guidance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first thought is to change the Foreign Key to use the Natural Key (362S162-33) instead of the Surrogate Key (15). Because you are using the Natural Key to communicate data between systems (the one exporting the CSV and the SQL Server importing it), using the Natural Key as a Foreign Key makes good business sense. As long as you declare a UNIQUE CONSTRAINT on the Natural Key, this is both practical and relationally sound.

    If that isn't an option, then you have two options which are essentially "variations on a theme" to accomplish the same effect. You can import the CSV data into a staging table, then insert the data into the production table using a lookup to get your surrogate key. You can also write a separate script or application which will do this lookup for you and will insert the row with the Surrogate Key in place.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2013
    Posts
    2

    Follow-Up

    Thank you for the information. I have read up on the differences (pro/con) between the Natural key vs. Surrogate Key approaches. I think it would be best for us to create a staging table then use the lookup function when inserting the data into the permanent table.

    As an alternate in regards to your final option, do you have suggestions on where to go to find someone to create a script that will do the lookup/insert of surrogate keys automatically?

    Thank you,
    Geoff

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by GeoffJ View Post
    We need to import data into a production table that has a "part number" (FK) column.
    Do not import directly to production tables.
    Create a staging table. Load to that. Then write a sproc that cleans, validates, and distributes the data according to your business rules and schema design.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    We can certainly talk you through the process of creating the script here on the forums, which will save you at least $100 to get someone to get acquainted with your database, business, etc. It also means that you'll need to invest some time and accept some risk if there are issues that aren't covered in the discussions or in your solution script.

    If you'd prefer to hire someone, I can recommend several people in your area. This will obviously cost money, but that might be money well spent if you get good suggestions and develop a longer term relationship with a local person that can support you in the future.

    If you'd like to work with someone remotely, I can recommend several people that can do that too. This would probably be lower cost, and possibly get you better talent but then you'd have to deal with the remote relationship... This is trivial for some people/companies and almost impossible for others.

    Let us know how you'd like to proceed, we'll help however we can!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •