SQL query or C# .net code for csv files import?

Question

My aim here is to find out the best possible and feasible solution for my dilemma. I want to import some csv file (may contain around 50~60K records) into database after some manipulation into the files, manipulation includes comparison such as one record already exists into database or not. I already have a .net c# application which

• gets the file
• fetches its data into datatable
• loop through each record, do comparison for each of the record from database
• and finally updates/creates/deletes record.

this whole process is apparently too time consuming. My client needs this process working as fast as possible. I suggested him to do this import process with SQL only(though I'm not sure whether it would work more efficiently using SQL features such as staging table, temp table and cursors).

Now by SQL this is what I thought could be done in order to get it working better:

• create a staging table
• using BULK IMPORT or SQLBULKCOPY function import all csv file's data into this staging table.
• create a SP which would loop through(using CURSOR ofcourse) all records from staging table and will compare each records from already existing db table and will create/update/delete record accordingly.
• empty staging table.

I'm not sure the SQL version would work more efficiently in my case or not. In total I'm not able to see a clear vision that whether I should go with the SQL version of solution or stick with the current .net C# code and optimize this current code only. It would be great if someone can advise me on the same.

---

let me explain this with the help of an example:

1. these are the headers of csv file: StudentId, StudentFname, StudentLname, ClassName, teacherId, teacherFname, teacherLname
2. suppose I've copied all its data as it is into staging table.
3. now I've to update those students which are already in STUDENT table and create those which do not exist, check if the classname already exists in CLASS table (if not then create class), check if the teacher id already exists in TEACHER table (if not then create teacher, otherwise update it).

Answer 1

SQL servers will offer specific products such as DTS or SSIS for importing data. These can be pretty optimised and run very quickly.

However!! If you have programming skills I highly recomend you write your own importing software.

The reason for this is that although the Sql server may be able to run an import process faster in a best case senario, it is inherently unscalable.

If you write your own importer then you can move all that logic and file reading off to a seperate server and only load your sql box with simple writes. This enables you to scale horizontaly.

SQL databases are one of the major pain points when scaling and the way to minimise this is to keep all business logic off the box. I have seen systems with complicated scheduling logic literaly running out of hours in the day for importing files, running a top spec sql box at 100% 24/7

Additonaly although tools lile DTS/SSIS are fine for DBAs who want to run the odd 'programming style' task. They fall well behind the features of a modern devops stack, such as versioning, debugging, no down time deployments etc etc

Answer 2

Not sure if you've resolved this by now. I agree the SQL route is probably the way to go, but I think your primary question is the best way in T-SQL to compare the staging data to the target table and insert/update as necessary, right? I think looping would end up being relatively slow for this scenario. I'd use a MERGE for the students, as Keith Miller said, and just regular INSERTS for class and teacher. For the sake of example, I made some assumptions about your data model, such as the Student table having a ClassId and TeacherId field.

INSERT INTO Teacher
SELECT  DISTINCT
        teacherId,
        teacherFname,
        teacherLname
FROM    StagingTable st
WHERE   NOT EXISTS (SELECT * FROM Teacher t WHERE t.teacherId = st.teacherId) 

INSERT INTO Class
SELECT  DISTINCT
        ClassName
FROM    StagingTable st
WHERE   NOT EXISTS (SELECT * FROM Class c WHERE c.ClassName = st.ClassName)

-- CTE here is optional, if you have a way to narrow the list of students to compare. 
-- For example, maybe the import file is for a specific school
; WITH StudentsCte AS
(
    SELECT  *
    FROM    Student
    WHERE   School = @school
)
MERGE StudentsCte AS Target
USING
    (
        SELECT  StudentId,
                StudentFName,
                StudentLName,
                ClassId,
                TeacherId
        FROM    StagingTable st
                INNER JOIN Class c ON c.ClassName = st.ClassName
    ) AS Source
ON Target.StudentId = Source.StudentId

WHEN MATCHED AND 
    (
        -- Optional comparisons to only update if something changed
        ISNULL(StudentFName, '') <> ISNULL(Source.StudentFName, '')
        -- OR (compare all fields)
    ) THEN 
    UPDATE SET 
        StudentFName = Source.StudentFName,
        ClassId = Source.ClassId
        -- other fields 

WHEN NOT MATCHED BY Target THEN
    INSERT (StudentFName, StudentLName, School, ClassId, TeacherId)
    VALUES (Source.StudentFName, Source.StudentLName, @school, ClassId, TeacherId)

WHEN NOT MATCHED BY Source THEN
    DELETE
    -- Or do a soft delete instead if the table has a flag like this: UPDATE SET IsDeleted = 1

This would need to be altered depending on your schema and relationships though. For example, if you use IDENTITY columns as your PK, and TeacherId is really an internal alphanumeric number unique to teachers (employee #, login name, etc.). In that case the Student FK to Teacher is probably a different field, and you'd have to join to teacher to get that ID value in the Source definition for the Merge.