Master Station Log
techno-rants, musings, misc geek writing, and occasional caffeine binges
SQL: You want me to update this Excel file?
Date: 2014-01-23 12:54:24
There have been plenty of times when I've been given an Excel spreadsheet of information and been asked to update the sheet with the current values from the SQL Database. Oh joy. Fortunately we can prep a SQL statement from inside the spreadsheet.
The idea is to use an Excel formula to make a SQL statement with the Excel fields. In long form, the formula is: (Equals) (Quote) INSERT INTO @ExcelTemp SELECT (Space) (Apostrophe) (Quote) (Ampersand) A2 (Ampersand) (Quote) (Apostrophe) (Comma) (Apostrophe) (Quote) (Ampersand) B2 (Ampersand ) (Quote) (Apostrophe) (Quote)
Now that we built those query strings in Excel we can copy/paste them into SQL right after the Temp Excel table (Lines 3-5). Join our Temp Excel table to our Database table (Lines 11-13). Now its just a matter of copying the results back into Excel!
DECLARE @ExcelTemp AS TABLE --This is the Temp Table to Hold the Excel info
([FirstName] VARCHAR(10), [LastName] VARCHAR(10))
INSERT INTO @ExcelTemp SELECT 'John','Smith'
INSERT INTO @ExcelTemp SELECT 'Jane','Doe'
INSERT INTO @ExcelTemp SELECT 'Mike','Jones'
DECLARE @DatabaseTemp AS TABLE --This is the table inside the database
([id] BIGINT, [FirstName] VARCHAR(10), [LastName] VARCHAR(10), [address] VARCHAR(50))
INSERT INTO @DatabaseTemp SELECT 1, 'John', 'Smith', '123 West Street'
SELECT E.FirstName, E.LastName, D.Address
FROM @ExcelTemp e
LEFT JOIN @DatabaseTemp d ON d.FirstName = e.FirstName AND d.LastName = e.LastName