Pages

Apr 25, 2011

How to update data from excel sheet to the sql data base. + How to compare two date time columns and find the later values

// Copies the excel sheet values to the table sTemp.
SELECT * INTO dbo.sTemp
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test.xls;IMEX=1',
'SELECT * FROM [Sheet_Name2$]')

GO

// Compares two columns of the excel and updates the table with the later values.
UPDATE INV
SET
INV.EndDate = TMP.
RecentDate 
FROM
dbo.Inventory as INV
INNER JOIN
(
SELECT
Inventoryid,
CASE
WHEN ISNULL([WEDate],'') >= ISNULL([MEDate],'')
THEN
[WEDate]
ELSE
[MEDate]
END
AS MostRecentDate,
[WEDate],
[MEDate]
FROM sTemp
) TMP
ON
TMP.TestID = INV.TestID

GO

DROP TAble stemp

GO