Database

Update D Set D,Field = S.Field
--Select *
from DestTable D inner join SourceTable S on D.Key = S.Key

Insert DestTable
Select Field1....
From SourceTable
Where KeyField not in(select keyfield from desttable)

SET ANSI_WARNINGS  OFF
MERGE PropertyLookupInformation as targetDB
USING TempCsvImport as sourceDB
ON targetDB.RealEstateNumber = sourceDB.RealEstateNumber
WHEN MATCHED
THEN 
UPDATE 
   SET Name = sourceDB.Name
      ,StreetNumber = sourceDB.StreetNumber
      ,StreetName = sourceDB.StreetName
      ,Type = sourceDB.Type
      ,Direction = sourceDB.Direction
      ,Unit = sourceDB.Unit
      ,City = sourceDB.City
      ,ZipCode = sourceDB.ZipCode
      ,LastReviewed = CURRENT_TIMESTAMP
WHEN NOT MATCHED BY TARGET
THEN 
INSERT 
           (PropertyID
           ,RealEstateNumber
           ,Name
           ,StreetNumber
           ,StreetName
           ,Type
           ,Direction
           ,Unit
           ,City
           ,ZipCode
           ,LastReviewed)
     VALUES
           ( NEWID()
           ,sourceDB.RealEstateNumber
           ,sourceDB.Name
           ,sourceDB.StreetNumber
           ,sourceDB.StreetName
           ,sourceDB.Type
           ,sourceDB.Direction
           ,sourceDB.Unit
           ,sourceDB.City
           ,sourceDB.ZipCode
           ,CURRENT_TIMESTAMP)
           ;

Devesh Sinha wrote:

Please provide me with some sample proc.

update pr 
set rostEntID =
(
    select TOP 1 e.recid
    from @Existing e, @dupes d2
    where
            e.refid = d2.refid
        and e.palias = d2.palias
        and e.palias2 = d2.palias2
        and e.pweight = d2.pweight
        and e.pHandicap = d2.phandicap
        and e.loginID = d2.loginid
        and e.password = d2.password
)
FROM participantresponses pr 
INNER JOIN @dupes d
ON pr.rostEntID = d.recID

SELECT MAX(TheDate), Type, Value 
FROM MyTable 
GROUP BY Type, Value

SELECT MAX(TheDate), Type
FROM   MyTable
GROUP BY TYPE

WITH maxdate AS (
    SELECT MAX(TheDate) TheDate, Type
    FROM MyTable
    GROUP BY Type
    )
SELECT  t.TheDate, t.Type, t.Value
FROM    MyTable t join maxdate d
    ON  t.TheDate = d.TheDate
    AND t.Type = d.Type

SELECT  TheDate, TYPE, Value
FROM    (
        SELECT TheDate, Type, Value, Rank( ) OVER (PARTITION BY Type ORDER BY TheDate DESC NULLS LAST) RN
        FROM MyTable
        )
WHERE   RN = 1

select distinct
(select top 1 t2.thedate from MyTable as t2 where t2.[type]=t1.[type] order by t2.thedate desc) as thedate
, t1.[type]
,(select top 1 t2.value from MyTable as t2 where t2.[type]=t1.[type] order by t2.thedate desc) as [Value]
from MyTable as t1
order by t1.[type]

select Value
      ,Type
  from TABLE
 where TheDate in ( select MAX(TheDate)
                      from Table
                     group by Type
                  )
;

MikeDhaan wrote:

and not the max date per type