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

[@more@]