SQL Server - Generate SQL INSERT from Excel

27 January,2012 by Tom Collins

Generate SQL Server INSERT statements with Excel Concatenate.

A developer requested  access to a Production database. The details were to COPY and PASTE from a Excel spreadsheet into Access , which then updated SQL Server.  Upon asking him , why he used this method , he said “It’s the way I always insert this data to the lookup table”.

There are better ways . One way , is to use the “Concatenate” function in Excel.  It concatenates the ascii portions of the statement, adding single quotes where relevant.

In this example , there are four rows with the values in col A and B.  Create the formula for the first line, the copy to  the rest of the lines.

Copy and Paste the the INSERT statements directly into SQL Server Management Studio or create a text file.


See Also

Powershell , Excel charts and data presentation

Excel Connect to SQL Server

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server - Generate SQL INSERT from Excel

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer