Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rocky
Starting Member
1 Post |
 Posted - 2007-12-10 : 16:27:03
|
| Let me see if I can explain what I'm trying to do (actually am doing now but with methods that I know can improve), and hopefully someone can give me some thoughts on the best way to do this (for maximum speed).I've got data that comes from a couple of different sources that I'm updating and compiling nightly. On each file there can be different fields of data (some memo fields, some numbers, some text, etc. but all a subset of a master list of fields in my table). I then need to take that data and basically update my existing records (or insert new records if they don't exist based on an id which is in the incoming files). The current program does this all by looping through the data and creating sql statements for the inserts and updates. I think using SSIS or bulk inserts after writing my data to a file, i should be able to speed this up. To me the "gotchas" are that each file may have different columns of data and I can't write different imports for each one. So I either have to have it be able to determine the columns for each piece of data and match when loading or updating or I could recode my program to basically insert the missing columns (should be doable).Any thoughts on the best way to do it? Writing out to a file for SSIS to use but having it only write out new records and then doing updates through another method or ? I know SQL Server 2005 fairly well for basics on SQL but haven't used SSIS so will have to learn or outsource so any good starter points or tips would help. Seems the updates are where I'm a bit more confused on the very best way to do it.I'm also stripping out html in one of the fields and creating a secondary field that doesn't have the html so I'm assuming the best is probably to do that while writing out the file I may use for whatever system but if there is a way to do it inside SQL or SSIS and make it happen quickly (so it doesn't lock the database) that would be great. |
|
|
tmitch
Yak Posting Veteran
60 Posts |
Posted - 2007-12-11 : 09:33:37
|
| You mention that each file may have different columns of data... can you be more specific? If you mean that the file structure may vary from one source to the next, you'll need to create separate logic for each source. However, if you mean that the source file from a single provider can vary from one export to the next, then you have a whole other issue to deal with.As far as the update vs. insert operations, you can either run a really nasty SQL statement to handle both (which I don't recommend), or you can use SSIS and create a fork using the Conditional Split component, or the Error Output of another component if you have unique constraints on your destination table. I prefer the latter, as it usually makes for a cleaner package.Hope this helps, feel free to post more information for additional help.---------------------Tim MitchellMitchell Information Consulting : www.mitchellic.com |
 |
|
|
|
|
|
|
|
Data importing and updating methods