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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy