Post reply

Update table from Flat File (500k+ rows)

  • August 31, 2006 at 5:29 am

    #115325

    Hi all,

    I'm trying to work out how to update a table from a flat file source.

    I could use the simple way of using OLE DB Command with

    UPDATE <tblName> SET Column1 = ?, Column2 = ?, <etc> WHERE ID = ?

    then using the mapping to join the fields.

    However, this is a slow process when you have potential 500k+ records in the source file (and approx 150 files to process!)

    What I would like to do is the following statement but I'm not sure if this is possible? 

    UPDATE <tblName> SET Column1 = sourceColumn1, Column2 = sourceColumn1, <etc>  FROM  <sourcefile> WHERE ID = sourceID

    I've also thought about importing the sourcefile into a Recordset Destination and trying to use the variable in the SQLCommand but I'm not sure how to do this...

    Does anybody have any ideas on how this could work or an alternative solution?

    Thanks in advance....

  • September 1, 2006 at 2:17 am

    #657965

    I understand that I could create a temp table and import into this. What I'd like to know is....

    I'd like to do the following

    1. Import the Flat File into a variable rsImportTable (Recordset Destination)
    2. Update <table> Set col1=impcol1 Select impcol1 from rsImportTable (OLE DB Command)

    Is this possible?

    If not then it would have to be a temp table

    1. Create Temp Table #Temp
    2. Import Flat File into #Temp
    3. Update/Delete using #Temp
    4. Drop Table #Temp

    As you can see, i think the less steps that I need to do will increase the preformance overall.

     

    Your thoughts...?

  • September 1, 2006 at 6:32 am

    #658004

    You can always create a linked server to a flat text file, but since I think it's not possible to index a text file, I don't see much of a performance gain...  You can always see if it's faster to do it that way depending on how much data needs to be processed, but I really can't tell you how it's gonna turn out.

     

    I personnally always used a staging table to do operations like that, but I never had to deal with that much volume, an also I always needed all the data contained in the file so my experience is quite limited in that situation.

  • September 7, 2006 at 1:53 am

    #658986

    I would go with the staging table approach (temp table). That gives you the most control and could give you a good 'patten' based approach to your ETL problem. I have daily processes running that ETL's around 30M rows a day and I have had no problems so far...

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

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