sp_generate_inserts page 1

Tweaked for 2005 from

http://vyaskn.tripod.com/code/generate_inserts.txt

CREATE PROC sp_generate_inserts
(
    @table_name varchar(776),          — The table/view for which the INSERT statements will be generated using the existing data
    @target_table varchar(776) = NULL,     — Use this parameter to specify a different table name into which the data will be inserted
    @include_column_list bit = 1,        — Use this parameter to include/ommit column list in the generated INSERT statement
    @from varchar(800) = NULL,         — Use this parameter to filter the rows based on a filter condition (using WHERE)
    @include_timestamp bit = 0,         — Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column’s data in the INSERT statement
    @debug_mode bit = 0,            — If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
    @owner varchar(64) = NULL,        — Use this parameter if you are not the owner of the table
    @ommit_images bit = 0,            — Use this parameter to generate INSERT statements by omitting the ‘image’ columns
    @ommit_identity bit = 0,        — Use this parameter to ommit the identity columns
    @top int = NULL,            — Use this parameter to generate INSERT statements only for the TOP n rows
    @cols_to_include varchar(8000) = NULL,    — List of columns to be included in the INSERT statement
    @cols_to_exclude varchar(8000) = NULL,    — List of columns to be excluded from the INSERT statement
    @disable_constraints bit = 0,        — When 1, disables foreign key constraints and enables them after the INSERT statements
    @ommit_computed_cols bit = 0        — When 1, computed columns will not be included in the INSERT statement
   
)
AS
BEGIN

/***********************************************************************************************************
Procedure:    sp_generate_inserts  (Build 22)
        (Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.)
                                         
Purpose:    To generate INSERT statements from existing data.
        These INSERTS can be executed to regenerate the data at some other location.
        This procedure is also useful to create a database setup, where in you can
        script your data along with your table definitions.

Written by:    Narayana Vyas Kondreddi
            http://vyaskn.tripod.com

Acknowledgements:
        Divya Kalra    — For beta testing
        Mark Charsley    — For reporting a problem with scripting uniqueidentifier columns with NULL values
        Artur Zeygman    — For helping me simplify a bit of code for handling non-dbo owned tables
        Joris Laperre   — For reporting a regression bug in handling text/ntext columns

Tested on:     SQL Server 7.0 and SQL Server 2000

Date created:    January 17th 2001 21:52 GMT

Date modified:    May 1st 2002 19:50 GMT

Email:         vyaskn@hotmail.com

NOTE:        This procedure may not work with tables with too many columns.
        Results can be unpredictable with huge text columns or SQL Server 2000’s sql_variant data types
        Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results
        IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed
        you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts
        like nchar and nvarchar
       

Example 1:    To generate INSERT statements for table ‘titles’:
       
        EXEC sp_generate_inserts ‘titles’

Example 2:     To ommit the column list in the INSERT statement: (Column list is included by default)
        IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below,
        to avoid erroneous results
       
        EXEC sp_generate_inserts ‘titles’, @include_column_list = 0

Example 3:    To generate INSERT statements for ‘titlesCopy’ table from ‘titles’ table:

        EXEC sp_generate_inserts ‘titles’, ‘titlesCopy’

Example 4:    To generate INSERT statements for ‘titles’ table for only those titles
        which contain the word ‘Computer’ in them:
        NOTE: Do not complicate the FROM or WHERE clause here. It’s assumed that you are good with T-SQL if you are using this parameter

        EXEC sp_generate_inserts ‘titles’, @from = "from titles where title like ‘%Computer%’"

Example 5:     To specify that you want to include TIMESTAMP column’s data as well in the INSERT statement:
        (By default TIMESTAMP column’s data is not scripted)

        EXEC sp_generate_inserts ‘titles’, @include_timestamp = 1

Example 6:    To print the debug information:
 
        EXEC sp_generate_inserts ‘titles’, @debug_mode = 1

Example 7:     If you are not the owner of the table, use @owner parameter to specify the owner name
        To use this option, you must have SELECT permissions on that table

        EXEC sp_generate_inserts Nickstable, @owner = ‘Nick’

Example 8:     To generate INSERT statements for the rest of the columns excluding images
        When using this otion, DO NOT set @include_column_list parameter to 0.

        EXEC sp_generate_inserts imgtable, @ommit_images = 1

Example 9:     To generate INSERT statements excluding (ommiting) IDENTITY columns:
        (By default IDENTITY columns are included in the INSERT statement)

        EXEC sp_generate_inserts mytable, @ommit_identity = 1

Example 10:     To generate INSERT statements for the TOP 10 rows in the table:
       
        EXEC sp_generate_inserts mytable, @top = 10

Example 11:     To generate INSERT statements with only those columns you want:
       
        EXEC sp_generate_inserts titles, @cols_to_include = "’title’,’title_id’,’au_id’"

Example 12:     To generate INSERT statements by omitting certain columns:
       
        EXEC sp_generate_inserts titles, @cols_to_exclude = "’title’,’title_id’,’au_id’"

Example 13:    To avoid checking the foreign key constraints while loading data with INSERT statements:
       
        EXEC sp_generate_inserts titles, @disable_constraints = 1

Example 14:     To exclude computed columns from the INSERT statement:
        EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1
***********************************************************************************************************/

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s