Someday, you might want to script the contents of SQL Server tables, along with your database creation script. Although there are other solutions too (like running a BCP import/export script, or writing a DTS), I think generating T-SQL code is the preferred way. This way, you can merge the database creation and data population into one T-SQL script, which can be run using OSQL or the Query Analyzer. The connection settings are separated from this script, because you have to connect using the Query Analyzer.

Original code

The basis for this code was created by Sumit Amar, and can be found on the Code project website. I've found some limitations in this script, which had to be solved:

• Dates were written in the date format of the server, instead of a fixed date format. This leads to problems, when you run the generated script on servers with different regional settings
• char and varchar values with single quotes (') were processed incorrectly. This leads to syntax errors in the generated script
• uniqueidentifier values were processed incorrectly. This leads to syntax errors in the generated script
• Field names with spaces and reserved words were not processed correctly. Now I use [fieldname] instead of fieldname, when I refer to fields

In my script, which currently has version number 2.1, these problems are solved.

Example output

INSERT external_path([externalpath_id],[name],[path],[last_updated],[updated_by]) VALUES('6FA4AC12-6919-4B9C-9C20-3AC49E605805','tissue','E:\',convert(datetime, '20060221 13:01:47.33', 112),'Paul.Deen') INSERT external_path([externalpath_id],[name],[path],[last_updated],[updated_by]) VALUES('7677BAD9-DD2D-4DF8-B7C4-557ED249C321','virus','E:\',convert(datetime, '20060221 13:01:50.997', 112),'Paul.Deen')

Download

Click here to download InsertGenerator 2.1 (6 kb)
(use right-click, and Save Target As...)

Notes

• In a future version, I will translate the documentation header, and remove commented code :-)
• For date format, I'm using a user-defined function called fn_dateiso, which has also been added to the script (at the top). In a future version, I will try to avoid the use of user-defined functions, because of the performance drawback, and dependancy

Last modified on: 2009-02-05