Update from multiple records

  • I'm in the process of creating a bulk import table and a 'destination' table that will hold the data that has been bulk inserted. The destination table will have one record, identified by an integer key, that should be updated with new data that is bulk inserted. If the key does not exist in the destination table, the new data should be added to the destination table.

    My question is how to do the update of an existing record, if more than one record with the key is in the bulk import table. For example, my bulk import table and destination table will look something like this:

    RecordTS [datetime],

    ObjectId [int],

    Value [int]

    If I have one record in the destination table:

    2009/08/28 01:00:00, 1, 55

    and I have three records that were brought into my bulk insert table:

    2009/08/28 01:01:00, 1, 56

    2009/08/28 01:02:00, 1, 57

    2009/08/28 01:03:00, 1, 58

    how can I do the update so the most recent record in the bulk insert table is the one used to update the destination table?

    Thanks

  • If you want a detailed answer to your question, read and follow the

    Forum Etiquette.[/url]

    Basically you want to use Max() and Group BY

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Since it is always the last record per person that you want to integrate, make use of the great 2005 feature : ROW_NUMBER() function.

    For a full documentation, please refer to BOL:

    http://msdn.microsoft.com/en-us/library/ms186734.aspx

    Basically, it should look like:

    WITH CTE AS

    (

    SELECT KeYCol, DATA1, DATA2,

    ROW_NUMBER ( ) OVER ( PARTITION BY KeYCol ORDER BY DATA2 DESC) as ORDINAL

    From MyTable

    )

    INSERT DetTable(KeYCol, DATA1, DATA2)

    Select KeYCol, DATA1, DATA2

    FROM

    CTE

    WHERE Ordinal=1

    REGARDS

    Tal Ben Yosef

    http://www.linkedin.com/in/benyos

  • OK - Here's the question once again with, hopefully, a format that follows the forum etiquette.

    The goal is to update the destination table with only the most recent record in the source table where the ObjectId's match.

    Thanks.

    --===== If the test tables already exist, drop

    IF OBJECT_ID('TempDB..#mysourcetable','U') IS NOT NULL

    DROP TABLE #mysourcetable

    IF OBJECT_ID('TempDB..#mydesttable','U') IS NOT NULL

    DROP TABLE #mydesttable

    --===== Create the test tables

    CREATE TABLE #mysourcetable

    (

    RecordTS DATETIME,

    ObjectId INT,

    Value INT

    )

    CREATE TABLE #mydesttable

    (

    RecordTS DATETIME,

    ObjectId INT,

    Value INT

    )

    --===== Setup any special required conditions especially where dates are concerned

    SET DATEFORMAT DMY

    --===== Insert the test data into the test table

    INSERT INTO #mysourcetable

    (RecordTS, ObjectId, Value)

    SELECT 'Oct 17 2007 12:00AM','1','55' UNION ALL

    SELECT 'Oct 17 2007 12:01AM','1','56' UNION ALL

    SELECT 'Oct 17 2007 12:02AM','1','57'

    --===== Insert the test data into the test table

    INSERT INTO #mydesttable

    (RecordTS, ObjectId, Value)

    SELECT 'Oct 16 2007 12:00AM','1','40'

  • Here's one way, but if the source table is large could be a performance nightmare

    --===== If the test tables already exist, drop

    IF OBJECT_ID('TempDB..#mysourcetable','U') IS NOT NULL

    DROP TABLE #mysourcetable

    IF OBJECT_ID('TempDB..#mydesttable','U') IS NOT NULL

    DROP TABLE #mydesttable

    --===== Create the test tables

    CREATE TABLE #mysourcetable

    (

    RecordTS DATETIME,

    ObjectId INT,

    Value INT

    )

    CREATE TABLE #mydesttable

    (

    RecordTS DATETIME,

    ObjectId INT,

    Value INT

    )

    --===== Setup any special required conditions especially where dates are concerned

    SET DATEFORMAT DMY

    --===== Insert the test data into the test table

    INSERT INTO #mysourcetable

    (RecordTS, ObjectId, Value)

    SELECT 'Oct 17 2007 12:00AM','1','55' UNION ALL

    SELECT 'Oct 17 2007 12:01AM','1','56' UNION ALL

    SELECT 'Oct 17 2007 12:02AM','1','57'

    --===== Insert the test data into the test table

    INSERT INTO #mydesttable

    (RecordTS, ObjectId, Value)

    SELECT 'Oct 16 2007 12:00AM','1','40'

    select * from #mysourcetable

    select * from #mydesttable

    update a

    set a.Value = c.Value

    from #mydesttable a

    inner join (select objectID, max(RecordTS) RecordTS from #mysourcetable group by ObjectID) b

    on a.ObjectId = b.ObjectID

    inner join #mysourcetable c

    on b.ObjectiD = c.ObjectiD

    and b.RecordTs = c.RecordTS

    select * from #mydesttable

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Try this approach.

    WITH UpdateRank AS (

    SELECT RecordTS, ObjectID, Value, Row_Number() OVER ( PARTITION BY ObjectID ORDER BY RecordTS Desc) AS RowNum

    FROM #mysourcetable

    )

    UPDATE #mydesttable

    SET RecordTS = u.RecordTS, Value = u.Value

    FROM #mydesttable AS m

    INNER JOIN UpdateRank AS u

    ON m.ObjectID = u.ObjectID

    WHERE RowNum = 1

    SELECT *

    FROM #mydesttable

    Also, I would recommend that anyone posting code use the IFCode shortcuts for code. They are [code="sql"] SQL Code here [/code] They can also be found on the left-hand side when you hit reply.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thanks everyone.

    I used benyos' reply to work out a solution and was testing it when drew.allen responded. My query looks like drew.allen's and appears to work.

    The part that I wasn't able to grasp before was the 'from' between the 'set' and 'inner join'...not sure why.

Viewing 7 posts - 1 through 6 (of 6 total)

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