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).

38 thoughts on “Using Excel to generate Inserts for SQL Server

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

    • 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.

    • Sam,

      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.


    • Sam,
      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 ๐Ÿ™‚

  2. 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?

    • Lars,

      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.

      • 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’)/


  3. Hello,

    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.


  4. 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)..

  5. 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

    • 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.

  6. 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.

  7. Sorry it was making sense until you got to the part when you said, “Skip a column or leave it for notes” then it doesn’t make sense without examples. Skip what column, where why? It also looks like you are just adding all the column names in anyway which is what a SQL insert requires. How is this different? Head scratch

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 )

Google photo

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

Connecting to %s