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
|
Dishwasher |
When can be hooked up with this formula to make a Union statement.
Formula
| Result
|
{="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'