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
Job Number - Value will be referenced with Job table to get
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.