Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data > Importing data from Excel – using INSERT statements

Importing data from Excel – using INSERT statements

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:

image

2. The source Excel file contains below data:

image

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:

image

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:

image

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

image

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

About these ads
  1. David Hutton
    March 19, 2013 at 10:05 pm

    I have done the same thing when one of my customer gave me a couple thousand rows to update based on the where of a column or two. It is fast and easy, great tip!

  2. DIya
    May 30, 2013 at 7:16 pm

    nice tip. thnx

  3. San
    December 19, 2013 at 4:30 pm

    I want to manipulate an empty cell and make it as NULL in the script. How would i do it. I’m new to this.

  4. Harishsingh
    March 4, 2014 at 6:06 pm

    Thanks For your help !!

  5. divya
    September 12, 2014 at 12:26 pm

    i had 110 cloumns in my table ,the above one is not working for my table ….

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 271 other followers

%d bloggers like this: