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
and
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 these ads

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

      Terry

    • 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 :D

    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′)/
        :D

  3. Pingback: 2010 in review « Golbing

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

    Tahnks

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

    http://leansoftware.net/en-us/help/excel-database-tasks/introduction.aspx

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