Have Geek Brain

Storage strategy for additional data along with imported data

I have encountered this problem several times and am wondering what other people are doing.

When I create a database, sometimes I have to regularly import data into a table, even if every day. I usually do delete all records and reimport each record from an external data source.

Many times, I will have to store some more data related to the imported records, but not from the original import source. Typically this "extra data" comes from user input. So, I will create another table with a primary key corresponding to the key of the table that receives the imported data, and store this additional data in a new table. If that doesn't make sense, here's an example:

In the old old system, we store employee data. But I need to use this data in a web application that cannot connect to this old legacy system. So, I am creating a database with a table that matches the data schema that I have on the old system, and every day I import each record into that table. When I do import, I drop every record and import every record.

But in my new system, employees can save bio. So in another table, I store this and their ID.

It would be easier to have only one table, but I can't do that because I would be deleting data that doesn't exist elsewhere when I do the import.

The other is bad because since I am deleting all these records for import, I cannot define foreign key constraints with associated data.

I hate developing databases this way because I know there is a better way. Wouldn't it be nice if I could do updates when I import the data rather than deleting and importing all of it?

I am using Sql server 2008, but I am curious about strategies that can work with any RDBMS.

0


a source to share


2 answers


Well, when you import, import into a temp table and then update the records in the worksheet (update in a general sense: delete deleted, add new, change what has changed).



You can also check out the new SQL command MERGE

in 2008, it can be very helpful for this case.

+2


a source


Here is a SQL Server 2008 merge expression I came up with to help me with my current situation:

MERGE INTO dbo.Sections as S        -- Target
USING dbo.SectionsStaging as SS     -- Source
ON S.Id = SS.Id                     -- Join
WHEN MATCHED THEN                   -- Record exists in both tables
    UPDATE SET
        TermCode = SS.TermCode,
        CourseTitle = SS.CourseTitle,
        CoursePrefix = SS.CoursePrefix,
        CourseNumber = SS.CourseNumber,
        SectionNumber = SS.SectionNumber,
        Capacity = SS.Capacity,
        Campus = SS.Campus,
        FacultyFirstName = SS.FacultyFirstName,
        FacultyLastName = SS.FacultyLastName,
        [Status] = SS.[Status],
        Enrollment = SS.Enrollment
WHEN NOT MATCHED THEN               -- Record exists only in source table
    INSERT ([Id],[TermCode],[CourseTitle],[CoursePrefix],[CourseNumber],[SectionNumber],[Capacity],[Campus],[FacultyFirstName],[FacultyLastName],[Status],[Enrollment])
    VALUES (SS.[Id],SS.[TermCode],SS.[CourseTitle],SS.[CoursePrefix],SS.[CourseNumber],SS.[SectionNumber],SS.[Capacity],SS.[Campus],SS.[FacultyFirstName],SS.[FacultyLastName],SS.[Status],SS.[Enrollment])
WHEN NOT MATCHED BY SOURCE THEN     -- Record exists only in target table
    DELETE;


Good material!

0


a source






More articles:


All Articles