2

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.

3

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

CREATE TABLE #TempTable(
    job_id   INT NOT NULL
    ,part_id  INT NOT NULL 
    ,quantity INT NOT NULL)

BULK INSERT #TempTable
FROM 'C:\example.csv'
WITH
(FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n') --any other options like FIRSTROW, etc

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

INSERT INTO MyTable
SELECT 
   job_id   
   ,part_id  
   ,quantity = SUM(quantity) 
FROM #TempTable
GROUP BY
   job_id   
   ,part_id  
  • 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. – Skitzafreak Jul 16 '18 at 19:31
  • No worries at all @Skitzafreak – scsimon Jul 16 '18 at 19:35

Your Answer

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

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