Importing data question.

Question

Importing data question.

jason.stephens

Ten Centuries

Points: 1088
More actions
January 10, 2011 at 10:58 am

#232841

I have a System Administrator that is receiving 3 csv files with employee information. They are all different in the respect that they hold different information about different types of employees. He has a table in a database for a employee directory. He wishes to not only import this information but do it regularly. I have never used SSIS but I think I got a weak handle on it now. My question is this...
He doesn't wish to update the entire table, only what has changed. What is the best way to go about this?
Should I have a SQL job fire off a SSIS package that pulls in all 3 cvs files, the current table and do some kind of join?
The reason he just wants to update things that have changed is because he will have custom fields that can be added to each employee that he doesn't want over-written.
Any suggestions?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Personally, I think I'd set up a query using OpenRowset to pull data from the CSV files, and Merge to upsert the data. That'll be the easiest way to go about it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

dkschill Hall of Fame Points: 3375 More actions · Answer 2

I am sure there is a simpler way to do it with SSIS, but if I were to do it using a simple tool set it I would use a staging table then run an insert where the unique key doesn't exist in the original table then update where the unique key does exist in the original table.

Something like this:

--STEP 1

CREATE TABLE tempLoadTable (with columns defined)

--STEP 2

--IMPORT FILES INTO tempLoadTable

--STEP 3

UPDATE OriginalTable SET OriginalTable.value1 = tempLoadTable.value1 , ... , OriginalTable.valueN = tempLoadTable.valueN

FROM tempLoadTable WHERE ISNULL(OriginalTable.unique_key,'-1') = ISNULL(tempLoadTable.unique_key,'-2') --CHECK FOR NULLS JUST IN CASE

--STEP 4

INSERT INTO OriginalTable (value1, ... ,valueN)

SELECT value1, ... ,valueN FROM tempLoadTable WHERE NOT EXISTS (SELECT 1 FROM OriginalTable WHERE ISNULL(OriginalTable.unique_key,'-1') = ISNULL(tempLoadTable.unique_key,'-2'))

--AGAIN CHECKING FOR NULLS

--STEP 5

DROP TABLE tempLoadTable

This would work, but I know that there are some cool tools within SSIS that could do it as well. For some reason I am intimidated by all the tools with SSIS and I work way to hard to not use them.

Rehman Rafique SSC Enthusiast Points: 178 More actions · Answer 3

yes schedule a job using ssis packageto import the files but create a instead of insert trigger to make sure that only the new or changed records are added to the table not the duplicate ones...