Professional Web Applications Themes

merge record - Microsoft SQL / MS SQL Server

When new data come in, it may be a brand new record, or an update of old record. So, I have to check the data, and choose either INSERT or UPDATE. Is there any SQL command that is similar to MERGE in Oracle 9i, so that only one statement can do both? Thanks, Guogang...

  1. #1

    Default merge record

    When new data come in, it may be a brand new record, or an update of old
    record. So, I have to check the data, and choose either INSERT or UPDATE.

    Is there any SQL command that is similar to MERGE in Oracle 9i, so that only
    one statement can do both?

    Thanks,
    Guogang



    Guogang Guest

  2. #2

    Default Re: merge record

    There isn't a single statement to do this but you can do it with two
    statements. Just do the UPDATE first. For example:

    UPDATE TargetTable
    SET col1 = (SELECT col1 FROM SourceTable WHERE keycol =
    TargetTable.keycol),
    col2 = (SELECT col2 FROM SourceTable WHERE keycol = TargetTable.keycol),
    col3 = (SELECT col3 FROM SourceTable WHERE keycol = TargetTable.keycol)
    WHERE EXISTS
    (SELECT * FROM SourceTable WHERE keycol = TargetTable.keycol)

    INSERT INTO TargetTable (keycol,col1,col2,col3)
    SELECT S.keycol, S.col1, S.col2, S.col3
    FROM SourceTable AS S
    LEFT JOIN TargetTable AS T ON S.keycol = T.keycol
    WHERE T.keycol IS NULL

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Guogang" <nospamno_such_domain.com> wrote in message
    news:OGF7kc$RDHA.1556TK2MSFTNGP10.phx.gbl...
    > When new data come in, it may be a brand new record, or an update of old
    > record. So, I have to check the data, and choose either INSERT or UPDATE.
    >
    > Is there any SQL command that is similar to MERGE in Oracle 9i, so that
    only
    > one statement can do both?
    >
    > Thanks,
    > Guogang
    >
    >
    >

    David Portas Guest

  3. #3

    Default Re: merge record

    Very nice, thanks.

    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:ui$yXp$RDHA.2252TK2MSFTNGP12.phx.gbl...
    > There isn't a single statement to do this but you can do it with two
    > statements. Just do the UPDATE first. For example:
    >
    > UPDATE TargetTable
    > SET col1 = (SELECT col1 FROM SourceTable WHERE keycol =
    > TargetTable.keycol),
    > col2 = (SELECT col2 FROM SourceTable WHERE keycol = TargetTable.keycol),
    > col3 = (SELECT col3 FROM SourceTable WHERE keycol = TargetTable.keycol)
    > WHERE EXISTS
    > (SELECT * FROM SourceTable WHERE keycol = TargetTable.keycol)
    >
    > INSERT INTO TargetTable (keycol,col1,col2,col3)
    > SELECT S.keycol, S.col1, S.col2, S.col3
    > FROM SourceTable AS S
    > LEFT JOIN TargetTable AS T ON S.keycol = T.keycol
    > WHERE T.keycol IS NULL
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    > "Guogang" <nospamno_such_domain.com> wrote in message
    > news:OGF7kc$RDHA.1556TK2MSFTNGP10.phx.gbl...
    > > When new data come in, it may be a brand new record, or an update of old
    > > record. So, I have to check the data, and choose either INSERT or
    UPDATE.
    > >
    > > Is there any SQL command that is similar to MERGE in Oracle 9i, so that
    > only
    > > one statement can do both?
    > >
    > > Thanks,
    > > Guogang
    > >
    > >
    > >
    >
    >

    Guogang Guest

Similar Threads

  1. How can i retrieve record ONLY From 300 - 400 in amillion Record Table?
    By keonglah in forum Coldfusion Database Access
    Replies: 6
    Last Post: June 13th, 02:53 AM
  2. Photoshop 7 Merge Linked vs. Merge Down
    By Seth_Thompson@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 0
    Last Post: February 25th, 08:14 PM
  3. Merge wont Merge Gradients?
    By Jason_Howard@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 1
    Last Post: February 13th, 07:52 PM
  4. Stop adding record in subform after record count = 1
    By Charlie in forum Microsoft Access
    Replies: 0
    Last Post: July 31st, 10:42 AM
  5. Adding a new record when the record source is a query.
    By Andy in forum Microsoft Access
    Replies: 0
    Last Post: July 29th, 01:41 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139