Rob Gonda's Blog

SQL Automated Insert Statements

Generating SQL scripts from ms-sql can sometimes be painful. SQL does a great job in generating sql schema scripts, including tables, views, functions, stored procedures, triggers, constraints, and indexes, but where's my data? SQL DTS (Data transformation services) can help if you're moving the data between databases, but what if you need to send someone a change script? or simply generate install scripts? I can't believe SQL provides no options for that.
Behold, I proses the solution! While researching this problem, I finally found a flexible stored procedure that will generate insert statements.

Check out this list of examples:

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

Download here the version for ms-sql 2000 or ms-sql 2005. I am sure there are some nice commercial solutions I should check out, so if you know any good one, please comment below.

Related Blog Entries

There are no trackbacks for this entry.

Trackback URL for this entry:

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
This blog is running version 5.9.003. Contact Blog Owner