monline

It's a fairly basic requirement to export data from a database table in the form of SQL "INSERT" instructions. However in case of Microsoft SQL Server it's not that simple. There's a utility called "Microsoft SQL Server Database Publishing Wizard", which comes packaged with SQL Server 2008, but has to be downloaded (and installed) separately for SQL Server 2005. Unfortunately it's not very sophisticated and here come the various 3rd party developers into the picture.

There're probably a number of scripts and tools available, I'll mention only two.

First a free one. A developer called Narayana has published a stored procedure that will output the INSERTs for any specified table that you've the right to access.

There's a GUI tool called SQL Scripter (requires .NET) which can create INSERTs too, but has a lot more options. You can filter the result set through WHERE conditions and can create conditional INSERTs (checks the existence of the record based on a unique column chosen by you). It can create INSERTs from arbitrary SQL queries too, thus you are not limited to the export of a single table (you just have to specify the target table to use in the generated INSERTs).

And finally you might want to check out the comments of this blog post for further tips/tools to look into.

P.S.: the MS utility "Database Publishing Wizard" is a pain in the ass when it comes to dealing with a table that has a few hundred columns. I used it on the EIM_CONTACT table of a Siebel CRM database (this table has 509 columns!). The table had only 3 records in it, but the generation of the SQL script took over 5 minutes. The same task was accomplished by SQL Scripter in 1s. I leave the conclusion up to you.