Post reply

Table updates

  • March 8, 2012 at 8:56 am

    #258372

    I am trying to update records between two tables given only a file mask to match the two.

    For example, the file has been renamed from 1733ActiveC_YYYYMMDD.txt to 1733Elig_YYYYMMDD.txt and exist in two seperate tables. I need to update the names between the tables to be the same. Below is what I have for one of the the file updates and it works by matching on the YYYYMMDD string. I have some 120 other files that I need to match in a similar way and am looking for some advice on how to best accomplish this?

    select groupid, enrollmentfileid, originalfilename, REPLACE(REPLACE(originalfilename, '1733ActiveC_', ''), '.txt', '') as TimeStamp, originalfiledatetime

    into #temp

    from dbo.enrollmentfile

    where originalfilename like '1733ActiveC_%.txt'

    begin tran

    update logfiletracker

    set [filename] = t.originalfilename

    from #temp t, logfiletracker l

    where t.groupid = l.groupnumber

    and t.TimeStamp in (Select REPLACE(REPLACE(filename, '1733Elig_', ''), '.txt', '') FROM logfiletracker)

    Thanks

    A clever person solves a problem. A wise person avoids it. ~ Einstein
    select cast (0x5365616E204465596F756E67 as varchar(128))

  • March 8, 2012 at 10:08 am

    #1456660

    in short:

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

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

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