Bradley Handziuk's blog

Joining strings to make a Select statement in Excel

I use Excel a lot to move data into SQL Server. But what I don't do is create a table in Excel, save it, import it into a database using the SQL Server Import and Export Wizard. I don't like that to have access to the data I'd have to create a table and remember to delete it.

Instead I think the easier route (especially with smaller datasets) is to turn the data in Excel into a Select statement which can be scripted into a #temp table or used directly.

Color Animal Place
Red Lion Africa
Blue Seal Antarctica
Purple Monkey

When can be hooked up with this formula to make a Union statement.

{="union select "&JoinStrings(",",$A$1:$C$1&"='"&A2:C2&"'")}
union select Color='Red',Animal='Lion',Place='Africa'
{="union select "&JoinStrings(",",$A$1:$C$1&"='"&A3:C3&"'")}
union select Color='Blue',Animal='Seal',Place='Antarctica'
{="union select "&JoinStrings(",",$A$1:$C$1&"='"&A4:C4&"'")} 
union select Color='Purple',Animal='Monkey',Place='Dishwasher'

The code for the JoinStrings can be found in another blog post here. Remember that the Excel formula has to be entered as an array formula (crl+shift+enter).

Now you have something you can just copy into SSMS, use once, and not have to worry about any cleanup:

select Color='Red',Animal='Lion',Place='Africa'
union select Color='Blue',Animal='Seal',Place='Antarctica'
union select Color='Purple',Animal='Monkey',Place='Dishwasher'