I am importing a flat file from a network share which is dropped there by an application and populating a table. Now there is an additional requirement from the application team to have these files perform updates to existing records. I'm thinking updates need to be scripted and run manually as a query rather than use the flat file import. Are updates possible (rather than inserts = simple) with a flat file? I'm thinking I could assign variables to the data fields in the file, look for a match with the primary key, and then perform the insert. This, however, seems overly complicated for a flat file import.
August 6, 2021 at 4:33 pm
load into a staging table
do joins to main target table - if existing record update, if new record insert . (do updates first)
even if you are using SSIS to do this you should still load into a staging table.
If you need updates, the easiest thing is to make a simple flat file import to a staging table. Then a query that inserts/updates based on the values in the staging table. A two step process.
August 6, 2021 at 4:34 pm
Don't forget to truncate the staging table before or after the process.
August 6, 2021 at 6:03 pm
Thanks -- I was thinking that too. I have it going to a temp table now (to scrub the data) but will look into adding a section for the Inserts/Updates.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply