Database

CREATE TABLE [dbo].[TempCsvImport](
    [RealEstateNumber] [nvarchar](11) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [StreetNumber] [nvarchar](10) NOT NULL,
    [StreetName] [nvarchar](50) NOT NULL,
    [Type] [nvarchar](10) NOT NULL,
    [Direction] [nvarchar](10) NULL,
    [Unit] [nvarchar](20) NULL,
    [City] [nvarchar](20) NOT NULL,
    [ZipCode] [nvarchar](10) NOT NULL)
GO 

SET QUOTED_IDENTIFIER OFF

DECLARE @fn varchar(8000)
SELECT @fn = 'C:\Users\owner\Documents\Visual Studio 2010\Projects\UPA Data Miner\UPA Data Miner\bin\Debug\634556001928942124.csv' 
DECLARE @cmd varchar(8000)
SET @cmd = "BULK INSERT dbo.[TempCsvImport] FROM '" + @fn + "' WITH(CODEPAGE='RAW',FIELDTERMINATOR = ',', ROWTERMINATOR='\n')"

EXEC(@cmd)

GO

UPDATE [dbo].[TempCsvImport]
   SET [ZipCode] = SUBSTRING(ZipCode,1,5)
 WHERE LEN([ZipCode]) != 5
 
GO

UPDATE [dbo].TempCsvImport
SET RealEstateNumber = REPLACE(RealEstateNumber,"-","")
WHERE CHARINDEX("-",RealEstateNumber) != 0

/*For each record in TempCsvImport*/

SELECT * FROM [dbo].[PropertyLookupInformation]
WHERE [dbo].[PropertyLookupInformation].[RealEstateNumber] = @RealEstateNumber
/*If a record is found*/
UPDATE [dbo].[PropertyLookupInformation]
   SET [Name] = @Name
      ,[StreetNumber] = @StreetNumber
      ,[StreetName] = @StreetName
      ,[Type] = @Type
      ,[Direction] = @Direction
      ,[Unit] = @Unit
      ,[City] = @City
      ,[ZipCode] = @ZipCode
      ,[LastReviewed] = CURRENT_TIMESTAMP
 WHERE RealEstateNumber = @RealEstateNumber

/*Otherwise insert*/ 

 INSERT INTO [dbo].[PropertyLookupInformation]
           ([PropertyID]
           ,[RealEstateNumber]
           ,[Name]
           ,[StreetNumber]
           ,[StreetName]
           ,[Type]
           ,[Direction]
           ,[Unit]
           ,[City]
           ,[ZipCode]
           ,[LastReviewed])
     VALUES
           ( NEWID()
           ,@RealEstateNumber
           ,@Name
           ,@StreetNumber
           ,@StreetName
           ,@Type
           ,@Direction
           ,@Unit
           ,@City
           ,@ZipCode
           ,CURRENT_TIMESTAMP)

/*Drop the temporary table.*/

DROP TABLE [dbo].[TempCsvImport]

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
                  )
;