I found this script that is able to generate insert T-SQL statements for data already existing in a database table. The script is packaged into a stored procedure and is thus very convenient to use.
It can reveal very useful when creating scripts for database initialization when packaging the release of a database driven application or moving some data to a new environment.
Developers do not always have access to all the different environments but sometimes still need to package data deployment scripts for those environments. Most often this will apply to lookup tables such as a lookup tables containing configuration values of an application which can potentially change with every release of an application. It could also apply to simpler cases such as a country code lookup table. Anyways, with this script, it will just take you seconds to generate all the insert statements into an SQL script that you can release with other deliverables (libraries, stored procedures…) to the release manager in charge of updating the application on the live environments.
The strong points of this script are that it handles identity column as well as computed columns and can script data for both views and tables. As this script is so useful for me, I backed-up the stored procedure scripts would the original link be one day broken:
I do not have SQL 2008 yet so I could check if the script works for SQL 2008. I guess that it might as SQL Server didn’t change as dramatically between the 2005 and 2008 version as it did between the 2000 and 2005 version. Anyway if someone tries it, please feel welcome to let me know about it in a comment 🙂
Note that commercial products are available would you need fancier data scripting features.