A wonderful example of how to generate inserts was given in the forum etiquette article. Of course to me, that was still too much typing. So I decided that I should write one script and just change the table name and let SQL generate the script to generate the script. Well since I was already sitting back and letting SQL do the work, I figured it should keep working. Initially I thought, why not store the output in a temp table or something and generate the insert statement like that. This seemed feasable but as I was getting ready to create more line generation I realized that the column name was going to be output whatever I chose to call it. So why not use it!? No one ever said I couldn't call a column [Fred] or [Geroge] or [INSERT INTO MyTable]. So I did 🙂
You will notice the output still carries the trailing 'UNION ALL' but you can certainly stop selecting when you get to that point. The underline for the column name being dashes is merely a comment so it can be sucked right up and run to do your inserts if you didn't feel like deleting it.
Another little trick I added here is the way I built up the column list. Notice the @Delim variable. Initially it is set to ''. Each time through the select it is set to ',' and used. This allows it to be blank the first time through and a comma all the other times. Because of that it can be added to the beginning of the string being built up and will not leave a trailing comma.