Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Don't forget the Title

By Absinthe,

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.

Total article views: 781 | Views in the last 30 days: 3
Related Articles

Generate Insert script

Generate Insert script


Generate INSERT for table with IDENTITY column

Generate a "INSERT INTO...SELECT FROM" script for a table with an identity column.


Generate Insert Scripts

Generate an insert of your data


SQL Script to generate columns for triggers

SQL Script to generate columns for triggers


Generating Insert Statements

This article from new author Oleg Netchaev describes the cursor-less script used to generate insert ...


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones