August 28, 2009 at 3:22 pm
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
August 28, 2009 at 4:00 pm
August 29, 2009 at 1:49 am
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
August 31, 2009 at 9:00 am
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'
August 31, 2009 at 1:36 pm
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/
September 1, 2009 at 10:20 am
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
September 1, 2009 at 10:35 am
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