Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    22

    Unanswered: Bulk Insert Help

    Ok so I have a csv that contains attendance records that I get daily from a 3rd party grade book solution. I need to import directly into the attend table in our student database.

    Code:
    Attend File-"1112","0021","404550","20120402","ABU","2300000","06","05"
    The file is setup as follows, School Year, school number, student_id, absence date, absence code, course number, section number.

    I need to check the student schedule to see if they are scheduled for that class when the import runs. So if they had a schedule change in the middle of the day it won't post attend to a dropped class.

    I have done something similar to this before with the way I export teachers out to our grade book. I have it check the master schedule to see if the teacher is teaching at least one class, that way it won't export tutors and office staff to the grade book. I used the script below to do that but not sure who to apply it to a bulk insert.

    Code:
     Script Used to export teachers note last four lines, checks master
    USE [GSchool]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[GradebookGetTeachers] 
    	@SchlYear varchar(10),
    	@SchlNum varchar(10)
    AS
     SELECT DISTINCT
    	@SchlNum,teacher_local_id,
    	ISNULL(HR_Code,'         '),Teacher_Last_Name,Teacher_First_Name,Teacher_Room,Teacher_Email
       FROM Teacher T
       WHERE School_Year=@SchlYear AND
    	Teacher_Status='A' AND 
    	Teacher_School_Num=@SchlNum AND
    	Teacher_Room<>'SFCC' AND ---Remove's College Teachers
    	EXISTS (SELECT * FROM Master_Schedule S 
    		WHERE T.Teacher_Local_ID = S.Teacher_SSN AND 
    		T.School_Year = S.School_Year AND
    		T.Teacher_School_Num = S.Location)
    Secondly I need to check the date of the record and overwrite a record if one already exists for that exact course and section for that student, I need this because if they make changes to a previous day from absence un excused to excused I need to get rid of the unexcused by overwriting it. I've never done anything like this so I'm kinda lost on this one.

    One more thing that would be nice but is optional, is there a way to send log of errors on the import via email?

    If I can just get the first two figured out then I will be golden. i appreciate the help in advance!

    Thanks
    Alex G.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create a staging table to store the imported data.
    Load the raw data directly into the staging table, and then use a stored procedure to validate, cleanse, and error-check the data prior to loading it into your production table.
    Include with your staging table a column for recording record-level errors that your script detects.
    Use the MERGE sql command to load the data into your production table(s) and avoid clashing with existing data.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Sep 2011
    Posts
    22
    Thanks for the reply, all that makes perfect sense and I have a pretty good idea how to accomplish that except for the error reporting, and how to email it every time the sp runs. Can you please elaborate?

    Thanks so much!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your stored procedure can update the Error Column of your staging table for each record that cannot be imported.
    At the end of you sproc, load all the validated data into the production table, and then delete the loaded records from your staging table.
    What's left at the end it every record that failed to import, plus the reason why it failed.

    You can set up and SSIS package that loads the staging data, executes the sproc, and then emails the contents of the staging table at then end of the process.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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