Database

CREATE FUNCTION dbo.LesserOf
(
  @Op1 NUMERIC(18,7)
,
  @Op2 NUMERIC(18,7)
)
RETURNS NUMERIC(18,7)
AS
BEGIN
  DECLARE @result NUMERIC(18,7)
 
  IF ( @Op1 < @Op2 )
    SET @result = @Op1
  ELSE
    SET @result = @Op2
 
  RETURN @result
END

Select  LocationID
       ,Sum(Case when MeetingID = 1 then Numvisitors else 0) as TotalNumVisitors1
       ,Sum(Case when MeetingID = 2 then Numvisitors else 0) as TotalNumVisitors2
From    Visitors
Group By LocationID

Select  LocationID, Groups.GroupSize
       ,Sum(Case when MeetingID = 1 then (Group)*GroupSize + Numvisitors else 0) as TotalNumVisitors1
       ,Sum(Case when MeetingID = 2 then (Group)*GroupSize +  Numvisitors else 0) as TotalNumVisitors2
From    Visitors
join Groups on Visitors.SomeID = Groups.SomeID
Group By LocationID, GroupSize 

Select  LocationID
       ,MeetingID
       ,Sum(Numvisitors) as TotalNumVisitors
From    Visitors
where   MeetingID IN (1,2)
Group By LocationID,MeetingID

print getdate()
execute yoursp
print getdate()

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.