I am working on importing data from an old non-relational database, to a new relational one. I am currently working on importing data into the following table:

Table: job_part
    job_id   INT NOT NULL --References ID Column of Job table
    part_id  INT NOT NULL --References ID Column of Part table
    quantity INT NOT NULL --Amount of parts required for the job
    PK = job_id & part_id

The data I have is in a .csv file which has the following columns;

Part Number - Value will be referenced with Part table to get part_id value

Job Number - Value will be referenced with Job table to get job_id value

Amount - Value to be stored in Quantity column

My problem is when there are data rows like the following:

CDDM 42, P0164, 1
CDDM 42, P0164, 2

As you can see, the Part Number, and Job Number are the same, while the quantity value is different. Because the table I want to insert this data into has the PartID and JobID as a composite primary key, these pieces of data will give me an error when I try to import it.

What I want to do, is to take this data, and before I put it in my table, put it into a temporary table that combines all of the matching key pairs. So for my problem example above, instead of having the two rows that will cause and error I want the result to be:

CDDM 42, P0164, 3

Essentially, find every matching pair of key values, and add their quantities together so they can be a single row of data.

However I am unsure how to go about solving this problem. I am using SQL Server.

1 Answer 1


You answered your own question, by importing it into a temporary or staging table.

    job_id   INT NOT NULL
    ,part_id  INT NOT NULL 
    ,quantity INT NOT NULL)

FROM 'C:\example.csv'
ROWTERMINATOR = '\n') --any other options like FIRSTROW, etc

Then, just use the SUM() aggregate when inserting into your actual table

   ,quantity = SUM(quantity) 
FROM #TempTable
  • 1
    I was more asking about how to do the SUM function, as I wasn't exactly sure how to code it. So thank you for the response. Commented Jul 16, 2018 at 19:31
  • No worries at all @Skitzafreak
    – S3S
    Commented Jul 16, 2018 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.