Friday, March 14, 2008

Generating SQL INSERT statements for existing data in SQL Server

I've been probably faced with the same problem of seed data during development and I've never managed to get around having a simple procedure to insert default data in some quick and automated way. Well, finally I've bumped into this absolutely amazing stored procedure that does exactly that. Get the procedure from here, courtesy of Narayana Vyas Kondreddi's code library. Works like a charm!

To create the INSERT statements for a selected table use:
EXEC sp_generate_inserts 'yourtablename'
To get data for all your tables you need to create one stored procedure call (as per above) for each table. Following SQL script will do just that:
SELECT 'EXEC sp_generate_inserts ' +
'[' + name + ']' +
',@owner = ' +
'[' + RTRIM(USER_NAME(uid)) + '],' +
'@ommit_images = 1, @disable_constraints = 1'
FROM sysobjects
WHERE type = 'U' AND
OBJECTPROPERTY(id,'ismsshipped') = 0
That's it. Above code will create one stored procedure call to create insert statements per table which you then need to execute separately to get the actual inserts. Really great script.

(updated 23 Mar 2008)
Another useful script for generating stored procedures for CRUD (found it here). It is a script file that you must change each time you run against a different database so I suppose it would make sense to put all that into a stored procedure.

2 comments:

david santos said...

Hello, Samir!
Excellent work.
Have a good weekend.

Jens Bennedsen said...

Super. If you use sqlserver2005 you need to remove the two calls EXEC master.dbo.sp_MS_upd_sysobj_category X
and replace it with
EXEC sys.sp_MS_marksystemobject sp_generate_inserts
after the definition of the stored procedure