You can easily import data from an Excel file to SQL Server using SQL Server Import and Export Wizard. However, when the data is simple and limited I avoid using it (too lazy to click through 6 screens… ) instead I generate INSERT statements using CONCATENATE function in Excel and execute those. This is much faster than using Import/Export Wizard.

This approach also requires the destination table to be created manually or it should already exists. Here’s how I use it:

1. The destination table I am using already exists with below columns, and some data:

2. The source Excel file contains below data:

3. Now to convert this to INSERT statement, we need to add a few columns to Excel sheet which will contain these texts "INSERT INTO dbo.ProductList VALUES (' ", " '' "," ' " etc. as shown below:

Note: if single quote (') is the first character in the column, as in Column C; you will need to input two single quotes (''). Also, if table has additional columns which are not being imported then you will need to include column list with INSERT statement i.e. "INSERT INTO dbo.ProductList (Name) VALUES (' " if you are importing only Name column.

4. Then you need to concatenate these columns using Excel's CONCATENATE function:

5. Finally, copy the same formula to all rows, these are the INSERT statements you need!:

6. That's all folks, Now copy them to SSMS and execute them…

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data