Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My Experiments with SQLServer
Browse by Tags · View All
SQLServer 126
SQLServer 2008 R2 91
SQLServer 2008 86
SQLServer 2005 82
SQL 60
Database 59
Sql And Me 57
Tips & Tricks 28
SQL Server 27
SQL FAQ 26

Archive · View All
May 2011 25
June 2011 21
July 2011 21
August 2011 19
April 2011 16
January 2013 4
May 2012 3
April 2012 3
October 2011 3
February 2013 2

Vishal Gajjar's Blog

Importing data from Excel – using INSERT statements…

Mar 29 2012 12:00AM by Vishal Gajjar   

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!

Facebook Page -> SqlAndMe


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


Republished from Sql&Me [31 clicks].  Read the original version here [4 clicks].

Vishal Gajjar
46 · 4% · 1276
3 Readers Liked this
Guru Samy Liked this on 3/30/2012 1:55:00 AM
Profile · Blog
anil Liked this on 4/16/2012 2:49:00 AM
Profile
vanne040 Liked this on 4/16/2012 9:06:00 AM
Profile
3
Liked
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

1  Comments  

  • Thanks for your sharing and then also possible to use this type in excel,

    ="INSERT INTO DBO.PRODUCTLIST VALUES ('"&A3&"','"&B3&"',"&C3&")"

    commented on Mar 30 2012 1:55AM
    Guru Samy
    9 · 16% · 5043

Your Comment


Sign Up or Login to post a comment.

"Importing data from Excel – using INSERT statements…" rated 5 out of 5 by 3 readers
Importing data from Excel – using INSERT statements… , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]