Generate Inserts.

Question

Post reply

Generate Inserts.

ben.brugman

SSChampion

Points: 13369

April 18, 2019 at 9:41 am

#3447614

From the past :

/*

Procedure: sp_generate_inserts (Build 22) 
(Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.)
http://vyaskn.tripod.com/code.htm#cat

*/

This procedure could generate the script for inserting Content for a table. There where a 'large' number of parameters to 'guide' the process.

Now we have :

databases/
   Testdb/
      Tasks...
         Generate Scripts ...
            Select specific database objects
               (select one or more tables)
                  Advanced
                     Types of data to script 
                           data only
                           schema only
                           schema and data
                          Save to file
                        ........

Using this 'sequence' in SSMS you can generate the script for the table (schema) and content (data) and save this to a file or to a window. It even generates a 'GO' every 100 rows. (nice).

The sp_generate_inserts can be called from within a stored procedure,

and has some nice parameters like :

@include_column_list -- which gives choice to include the column_list or not.

@from -- which gives the oppertunity to use a Where Clause.

Is there a 'combination' of both ? Or a more modern version of sp_generate_inserts ?

Call-able from a stored procedure, with the 'nice' parameters, with a 'GO' every 100 (or other number) rows ?

Thanks for your time and attention,

Ben

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

I've used that stored procedure since he first wrote it - great little utility. There are a few people who have done some updates to that stored procedure - search on sp_generate_inserts and you should be able to find a couple updated ones. But that stored procedure was written for data only. If what you are looking for is an update that allows data only, schema only, data and schema then I'm not aware of a rewrite of that procedure that does that. It's a lot different than what Vyas originally wrote. There was no way to script just data when that procedure was written.

Sue

ben.brugman SSChampion Points: 13369 More actions · Answer 2

Thanks Sue,

Yes I have been using that stored procedure also for a long time. At the moment I am generating the Meta data and the content. The meta is not a problem. For the content, I do run into the problem that there are to many rows to be processed within a file. (A copy/paste problem, and a 'to large' problem.) At the moment I am solving this by placing 'GO' lines every 10 000 rows.

The generate script in SSMS does place a Go / print line every 100 rows, this prevents problems.

So both features I 'need' at the moment are available, just not in the same tool. I do not mind building my own tools, I even like that. But if a tool which does what you need is available, this is often a better choice. So hence my question is something available. If so use it, if not go for another solution or (re)build a similar tool. (If all building and rebuilding of tools was bunched together, this community could come of with an awfull lot of good and handy tools).

Ben

Sue_H SSC Guru Points: 90860 More actions · Answer 3

All of the scripting options used in SSMS generate scripts are exposed, available programmatically when using SMO. There are quite a few different sample scripts available using SMO but it depends on what combinations of what you would want. But you can generate scripts using SMO and Powershell so maybe that's along the lines of what you are looking for. Check the Automated scripting of static data section in the following article - it may not have exactly what you are looking for but the script should help get started:

https://www.red-gate.com/simple-talk/sql/database-administration/automated-script-generation-with-powershell-and-smo/

And here is the documentation with the options available:

https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.scriptingoptions?view=sql-smo-140.17283.0

Sue