Merging two tables

Question

Post reply

Merging two tables

ss_nl

Say Hey Kid

Points: 666
More actions
August 11, 2010 at 3:18 am

#387548

There are two tables I want to join or merge, but need some help with the query.
Table1 = archive table
Table2 = current table
Result of the merge should contain:
- Unique list of all objectId's from both tables, either existing in table1, table2 or both.
- Latest data values from table2 (where max(lastModified)
- If objectId not in table2, then last data value from table1
How can I do that in one query?

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Post the table defintion/index defintion and sample data along with desired sample output.

See how to post queries article from my signature:-)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

-- make some sample data

DROP TABLE #Archive

DROP TABLE #Current

CREATE TABLE #Current (objectId INT IDENTITY(1,1), Data1 VARCHAR(5), lastModified DATETIME)

INSERT INTO #Current (Data1, lastModified)

SELECT 'A', GETDATE()-5 UNION ALL

SELECT 'B', GETDATE()-4 UNION ALL

SELECT 'C', GETDATE()-3 UNION ALL

SELECT 'D', GETDATE()-2 UNION ALL

SELECT 'E', GETDATE()-1 UNION ALL

SELECT 'F', GETDATE()

CREATE TABLE #Archive (objectId INT, Data1 VARCHAR(5), lastModified DATETIME)

INSERT INTO #Archive (objectId, Data1, lastModified)

SELECT objectId, Data1, lastModified

FROM #Current

UNION ALL

SELECT objectId, Data1 = 'BB', lastModified+1

FROM #Current

WHERE objectId = 2

DELETE FROM #Archive WHERE objectId = 3

DELETE FROM #Current WHERE objectId = 2

-- make a test case query:

-- label the most recent row per objectId as RowID = 1

;WITH CTEArchive AS (

SELECT RowID = ROW_NUMBER() OVER (PARTITION BY objectId ORDER BY lastModified DESC),

objectId, Data1, lastModified FROM #Archive

)

SELECT

objectId = ISNULL(c.objectId, a.objectId),

Data1 = ISNULL(c.Data1, a.Data1),

lastModified = ISNULL(c.lastModified, a.lastModified),

'#' AS '#', -- see the values of #Current and CTEArchive

c.objectId, c.Data1, c.lastModified,

a.objectId, a.Data1, a.lastModified

FROM #Current c

FULL OUTER JOIN CTEArchive a ON a.objectId = c.objectId

WHERE a.RowID = 1 OR a.RowID IS NULL

ORDER BY 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden