Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Writing SQL with Mail merge (All)

    I was grumbling away at work today when an email arrived and it had a list of old codes next to a list of newcodes which the business needed me to update in a table on my SQL database. "We need this for the morning Jezza" was the message. There are about 500 updates to do and I have been told to do it after hours.....money is good so no probs there <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    I worked out a very simple method to write the update code and I thought I would share it with you.

    1) Ensure all the codes are in two columns in an excel workbook
    2) If they are not there already create a header row and name each column (OldCode, NewCode)
    3) Save Workbook

    Open a word document

    4) Write your code and where the OldCode and NewCode need to be insert as Mailmerge fields referencing your newly saved Excel workbook above.

    tblMyTable
    SET myField =<<NewCode>>
    WHERE myField = <[list=1]>

    Run the mailmerge and 500 updates are created, with a little bit of manipultion in Word you can get all the merged dat together in one long list and it is a matter of just copying and pasting into your desired application, mine being Enterprise Manager or you Access Database SQL area.

    Thought I would share as I thought is was a nice Top Tip <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Writing SQL with Mail merge (All)

    A bit shorter: let's say the table is named tblMyTable and the field to be updated is myField.
    1) Import or copy/paste the updates into a table tblUpdates with two text fields OldCode and NewCode. Create a unique index (e.g. the primary key) on OldCode.
    2) Create an update query:

    UPDATE tblMyTable INNER JOIN tblUpdates ON tblMyTable.myField = tblUpdates.OldCode SET tblMyTable.myField = tblUpdates.NewCode

    3) Run the query.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Writing SQL with Mail merge (All)

    Yep, nice one.

    Funnily enough I am about to run my little bit of code now and have shut the web services off the application to do it

    I am unable to do this on my SQL server as it is a specific application but your method is very useful for Access databases <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Writing SQL with Mail merge (All)

    Your method is an interesting (and highly original) application of mail merge. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •