Scripting INSERTs

  • Tony Webster

    SSChasing Mays

    Points: 610

    Comments posted to this topic are about the item Scripting INSERTs

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice article...

  • Adam Tappis

    SSC Journeyman

    Points: 98

    Nicely done. However I would look to apply the the filter condition as early as possible, because on a 1 million row table, the procedure will cursor over all 1 million rows even if the condition limits the result to just 3 rows...

  • Mike Sofen-356112

    Valued Member

    Points: 57

    Suggested revisions: eliminate the cursor with a "while" loop, and use the metadata more effectively to eliminate the function calls. The input param that is nullable simply needs the standard "= null", rather than " = '1=1' ". Use "set nocount on" at the beginning of the sproc to eliminate the bazillion return messages this sproc generates.

    And the biggies: you're taking the top 100 columns from a table. That means, if a table has 107 columns, then you'll only script 100 of them...does that make any sense? If indeed there is an obscure sql fault on greater than 100 columns, then skip the table and return a message to the user saying just that. Ditto for the text column...don't partially script tables. Also, you strangely AREN'T filtering for calculated columns, which you cannot insert values for...those columns need to be remove from the #Fields table.

    Mike Sofen

    San Diego, CA

  • grant.owens

    Old Hand

    Points: 351

    Nice stuff and very useful. There is a more sophisticated version of this at http://vyaskn.tripod.com/code.htm#inserts that i've used fairly extensively.

  • Etni

    Valued Member

    Points: 68

    google sp_generate_inserts

  • dfarran

    Grasshopper

    Points: 16

    Nice article. You can actually drop the cursor completely and replace with the following

    DECLARE @fields nvarchar(4000)

    DECLARE @values nvarchar(4000)

    SET @fields = ''

    SET @values = ''

    SELECT @fields = @fields + ',[' + sc.name + ']' ,

    @values = @values + '+'',''+dbo.'

    + CASE WHEN st.name = 'money' THEN 'spMVal'

    ELSE 'spVal'

    END

    + '([' + sc.name + '])'

    FROM syscolumns sc

    JOIN sysobjects so ON so.id = sc.id

    JOIN systypes st ON st.xusertype = sc.xusertype

    WHERE so.name = @table

    AND st.name <> 'text'

    ORDER BY sc.colorder

    PRINT @fields

    PRINT @values

    In SQL 2000 there is the (mentioned in the caveats) issue with nvarchar(4000) meaning you will potentially run out of string space, this is much less of an issue in 2005 with nvarchar(MAX).

    I hope someone finds this SELECT @STR = @STR + ... useful, I know I have. I have found some occasions where it only returns one line, but if you include TOP 100 percent it will generally solve this issue.

  • Tony Webster

    SSChasing Mays

    Points: 610

    That means, if a table has 107 columns, then you'll only script 100 of them...does that make any sense? If indeed there is an obscure sql fault on greater than 100 columns, then skip the table and return a message to the user saying just that. Ditto for the text column...don't partially script tables. Also, you strangely AREN'T filtering for calculated columns, which you cannot insert values for...those columns need to be remove from the #Fields table.

    All good points - this was never indented to be complete to that degree. What actually is more likely to happen before you hit 100 columns I think is that the varchar variable used to assemble the SQL would overflow its length.

    I don't actually run what it produces without checking it visually first. All I was after was something simple to fulfill a particular need - a plank to get over the stream at the bottom of the garden - a fully stress-tested suspension bridge it certainly isn't! That said, there are some good ideas here and elsewhere in the thread for improving it relatively simply.

  • Alexander Kuznetsov

    SSCrazy

    Points: 2217

    I've been using sp_generate_inserts for a long time, no problems:

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

  • Yelena Varshal

    SSC-Dedicated

    Points: 34270

    Why Insert... Select would not do?

    Regards,Yelena Varsha

  • emiddlebrooks

    Hall of Fame

    Points: 3116

    Yelena Varshal (12/29/2008)


    Why Insert... Select would not do?

    I assume that your question is why you couldn't directly insert into one DB by selecting the data from another?

    This is meant to be more portable than that so that you could ship the script from one place to another without being connected. Personally I do this a lot via email to a client so that all they have to do is open the script and run it.

  • Yelena Varshal

    SSC-Dedicated

    Points: 34270

    I mostly use CSV / BCP or MS ACCESS for that.

    Regards,Yelena Varsha

  • Mike Sofen-356112

    Valued Member

    Points: 57

    This type of query is used when we need to create a script that mirrors the data in a given table to a sql script. This often used to place a base taxonomy into source control, or to create build scripts for a new server, or rollback scripts for a release. In all of these cases, we need the actual insert statements that will create the data, not the data by itself. Make sense?

  • luckyHailong

    SSC Rookie

    Points: 36

    if the data has "--" , "[" or other keyword does it work?

  • NewOzzie

    Valued Member

    Points: 74

    This is also not catering for binary/varbinary columns correctly

Viewing 15 posts - 1 through 15 (of 23 total)

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