get latest date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

get latest date

Author

Topic

shar_wani
Starting Member

2 Posts

Posted - 2012-07-11 : 03:22:56

the problem is like this.

i've staging table which one group will be inserting data even the same data for every column. means the possibility to have a duplicate data is high.

i need to insert data from staging table into main table. at main table, there's a constraint which didn't allowed duplicate data to be inserted.

so, how should i get the latest data from staging table to be able to insert into main table?

the type for update_date column is DATE.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-11 : 07:33:58

Something like this:

;WITH cte AS
(
	SELECT
		col1,col2, .. colN, 
		ROW_NUMBER() OVER (PARTITION BY col1,col2, .. colN ORDER BY update_date DESC) AS RN
	FROM YourStagingTable
)
INSERT INTO yourDestinationTable
	(col1, col2, .. colN)
SELECT 
	col1,col2, .. colN
FROM
	cte
WHERE
	RN = 1;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-11 : 10:35:11

[code]
SELECT col1,col2, .. colN
FROM
(
SELECT
col1,col2, .. colN,
MAX(update_date) OVER (PARTITION BY col1,col2, .. colN) AS MaxDate
FROM YourStagingTable

)t
WHERE MaxDate = update_date
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources