Using Excel to generate Inserts for SQL Server

Much of my day job involves database design, maintenance and manipulation. I use SQL scripts to quickly build my databases from scratch. In order to test them I need piles of data and find it is often too much work to generate all those inserts manually or figure out sqlcmd or osql code to import bulk data.
The best programmers, as many say, are lazy and the quickest way I’ve found to generate the inserts is to use Excel. If any of the following isn’t clear please attach a comment and I’ll be glad to explain in further detail.
Doing everything with scripts is certainly doable but the changes and administration can be a real pain. So I use Excel formulae to generate the data insert scripts for me. The first step is to lay my data out in columns in a spreadsheet. A quick way to do this using SQL Server Management Studio is to choose the option that allows the headers to be exported along with the data and then just copy the current data from a database table into a spreadsheet using the clipboard. Skip a column (or use it for notes) and then type something like the following formula in it:
="insert into tblyourtablename (yourkeyID_pk, intmine, strval) values ("&A4&", "&B4&", N'"&C4&"')"
Now you’ve got your insert statement for a table with your primary key (PK), an integer and a unicode string. Now all you need to do is to use Excel’s fill tools to copy data and the formulae down the rows and then just copy the whole column and insert into a SQL script. Including PK’s requires statements like
set identity_insert tblyourtablename on
set identity_insert tblyourtablename off
to bracket your insert statements. But if you want to avoid that hassle, you can skip the PK’s as I do in this statement:
="insert into tblyourtablename (intmine, strval) values ("&B4&", N'"&C4&"')"
Of course, if you skip the PK’s then you need to keep any foreign keys you may be using straight. How do you do that you might ask? It’s all doable. You simply arrange the order of things if you like to keep the PK’s. So your data insert script would have lookup table inserts at the top, and then those for tables with foreign keys later.
Or you get fancy and make your scripts relative (you can use your inserts regardless of whether there’s data already present or no) by using variables. In this way if you have a set of tables that rely on each other you could chain the insert statements together with semi colons and local variables for the foreign keys. Here’s an example:
declare @intpane int;
="insert into tblyourtablename (intmine, strval) values ("&B4&", N'"&C4&"'); set @intpane = scope_identity(); INSERT INTO tblpane (nameid_fk,strtext,bitmine,vbarmine) VALUES (@intpane ,N'"&D8&"' ,0 ,convert(varbinary,''));"

Bear in mind that you must copy the above into your SQL script by selecting the entire block if you have formulae in multiple columns.
There are a few tricks to keep in mind.

  • The text designator in SQL is the ‘ whereas it is the ” (double quote) in Excel.
  • Sometimes you need to insert text that contains a ‘ and that can really mess your code up so you need to put two ‘ together in that data column. For example the name O’Brien needs to be entered in the data column in Excel as O”Brien (this is easily done with a simple search and replace).
  • Remember to enter “””” if you need to print a ” with Excel.
  • Date/time fields are inserted like text in SQL (they use the ‘ too, like in ‘1956-09-01 13:24:16.000’, I find it easier to use 000 for the milliseconds because in SQL they go up by 3.33 and so always round to end in 0, 3 or 7).

About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , , , . Bookmark the permalink.

35 Responses to Using Excel to generate Inserts for SQL Server

  1. Michael says:

    Thanks, your post helped me alot!

  2. Bill Howard says:

    Many thanks, a simple yet very powerful solution that is much better than my previous attempt at conversion!

  3. Murli says:

    Sorry its not clear to me can you please provide any example

  4. Jason says:

    Outstanding. This saved me hours of work.

  5. Curious says:

    It will works if the sql statement is short. if sql statement quite long it won’t work. Thanks

    • tgrignon says:

      I have very long SQL statements on a single line, they can be many hundreds of characters long and separated by semicolons, and I haven’t found a limit to the length in SQL Server Management Studio yet. It is true that there is a limit in Excel for the number of characters expressed in a text formula (# of characters that can be stored and displayed in a cell formatted as Text is 255 but in Excel 12 or 2007 the new Limit is 32k although I don’t have that version yet to try) but this can be overcome by chaining your formulae. By ending your lengthy formula part way through with a double quote and then putting in a new formula in the next column over which refers to the first cell and carries on you can achieve very long text strings.

  6. Maryann says:

    Thank You! Thank You! Thank You! ๐Ÿ™‚

  7. Sam Howley says:

    Hi tgrignon,

    I have created a tool called QueryCell, that makes it easier to generate SQL insert statements from Excel data :

    I would love to hear your feedback on QueryCell and would be happy to provide some free licenses for you to give away to your readers.

    Please get in contact if you would like to take a look

    Sam Howley

    • tgrignon says:


      Sorry to waiting this long to respond… I did try your solution as soon as you sent it to me but I obviously didn’t reply here. I forget if I responded by e-mail or not but I do remember your tool. It’s pretty good and I would recommend it to those who want a quick way to achieve the goal without having to create all those worksheets.
      I guess that for those, like me, who have done this work already, the amount of work when a change comes or new data is needed is relatively small. It’s a self-fulfilling prophecy in a way.
      But, still, I like your tool.


    • mydatingadventureLars K says:

      I just saw your tool and it looks very promising for what I need to do. I’m actually working on a solution for a non-profit organization and they need to fetch some reports from the sql server. If you’d like to donate a license, that would probably save them a few bucks.

      Please reply if you’d be interested and I could check with you if the tool would actually fulfill some of their needs.

      Cheers ๐Ÿ™‚

  8. Lars says:

    This was great, and as you said – lazy. My kind of solution ๐Ÿ˜€

    Only one thing I’m wondering about. My numbers in the first column are padded with zeros to make them four characters wide. This doesn’t carry over to the query. Do you know of any way to do this?

    • tgrignon says:


      I’m not sure I understand what you’re trying to do. If you try to put padded zero numbers into a primary key field in SQL Server it will just translate them to regular integers. If you want padded digits in Excel then you’d just format the field accordingly but, like I said above, it won’t last through to SQL. If you really need a padded zero number for each row I would create a calculated field in SQL that builds it although that may require some fancy footwork in code to achieve.

      • Lars says:

        I don’t need an id for that table, so I was even more lazy:
        s/^(\w{4})\t(.*?)\t.*$/insert into ecotech_postnummer (postnummer, poststed) values (‘$1’, ‘$2’)/


  9. Hey we have started contest to win Querycell excel add-on by which you can Generate SQL Script from Excel on a single click of mouse.

  10. Pingback: 2010 in review « Golbing

  11. I used to deal with the same problem, until found this awsome and free tool.


  12. shilpa says:

    thanks for ur help…
    keep posting…..!!!

  13. Lohit Singh says:

    Great……………………………….. Thanks

  14. Scott Phillips says:


    My name is Scott and I fairly new to SQL.. With that being said, I will try my best to explain what I am trying to accomplish with Excel and SQL intregration. Currently I have multple DBs. All of these DBs can be access via Excel’s Connection wizard as long as the correct credentials are entered. That is simple!!!

    My ulitimate goal is to add, delete, or modify existing DB data via Excel. I would also like to create queries that will generate reports for my co-workers.

    Any expertise will be greatly apprecitive.


  15. faizan says:


    for simple data loads, copy and paste might come in handy

    But steps you mentioned will still be great when someone else is going to load the data ( prod deployment )

  16. seo says:

    I appreciate your wp template, where did you download it from?

  17. toto says:

    Helped me a lot. Much easier than these wizzard to create update statements. Thanks

  18. karthik says:

    This is a time saver. Thank You.

  19. Richard says:

    For a permanent reusable solution you might like to check out the EDT tool.

    The tool will allow to create highly configurable tasks (saved as XML files) that can perform all sorts of operations such as sending pasted data to a table, or stored procedure. The tool handles data validation and transaction handling – so you don’t end up with imports failing half way through!.

    In addition you can create data edit tasks, or data transfer / update tasks from any data source.

    Check it out: (EDT is used by Nissan Motor Manufacturing and many other companies)..

  20. sergburk says:

    Good. Thank you!

  21. Pingback: How to copy data from Excel to SQL | Code Jamming

  22. Syreeta says:

    I can’t believe the amount of bloggers and supposed SEO professionals who spread misinformation
    Using Excel to generate Inserts for SQL Server – The newest addition to my RSS feed

  23. Tapiwa says:

    Hi, Thanks for the tip. But How do i connect to my SQL db from Excel?

    • tgrignon says:

      There are ways to do that but none are necessary or probably advisable to do with what this tip suggests. You can simply copy up the generated T-SQL using your clipboard and then paste it into a SQL Server Management Studio new script.

  24. sarahemmm says:

    Many thanks! I have used your idea with great success – very easy to do.

  25. Pingback: Import Excel spreadsheet columns into SQL Server database | ASK AND ANSWER

  26. Peter says:


    I have created this VBA scrip which will generate SQL insert scrip for you. It can also recognise the datatype so it will format correctly BIT and DATE / DATETIME data format.

  27. Miguel says:

    I used to do the string concatenation thing but then I found this tool

  28. Love the internet. After spending almost a week trying to insert a recursive program’s results into a table with one item refusing to cooperate, I tried your method after running the program into Excel (I know, it can output into Excel but not a SQL table…) I ran a 16 column, 2951 row spreadsheet into the SQL table in 4 seconds. Brilliant…thanks.

Leave a Reply

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

You are commenting using your 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