Generating Insert Statements

  • Comments posted to this topic are about the item Generating Insert Statements

  • Excellent article!

    Actually exactly what I needed!

    Coming from SQL Server over MySQL and back to SQL Server - suddenly - it annoyed me as @#%& that moving from development to production there was no way of moving the data. (Yes: backup and restore, but in a live environment you don't really want to do that, do you?)

    I will tweak it a bit - also generating the actual table prior inserting.

    Hopefully I will get it done and I'll post it here - if no one beats me to it...

  • Nice idea.

    An alternative is to use SSMS 2008's in-built data scripting functionality:

    http://www.sqlskills.com/BLOGS/PAUL/post/Scripting-schema-AND-data-with-SSMS-in-SQL-2008.aspx

    Chris

  • It is an amazing idea.

  • Looking forward to adapting your code to our environment. Excellent article -- well written. You've raised the bar for other authors.

    One small nit -- it's couldn't care less.

    Hope to see you post more.

  • Nice article 🙂 and really well written.

    Same idea as mine:

    http://www.sqlservercentral.com/scripts/Script+Data/65998/

  • Brilliant!!! Outside-the-box thinking that turns conventional processes on their head.

    Thank you, Oleg.

    Jeff Bennett

    SQL DBA

    Saint Louis, MO

  • Chris Howarth (7/1/2009)


    Nice idea.

    An alternative is to use SSMS 2008's in-built data scripting functionality:

    http://www.sqlskills.com/BLOGS/PAUL/post/Scripting-schema-AND-data-with-SSMS-in-SQL-2008.aspx

    Chris

    I just love it when I start the day off learning something new. With this link, and with the article showing how to do the inserts as binary data, I've learned two things.

    Great article, and good link here ... I didn't know that this was in SSMS2008.

    Edit: quoted wrong message when posting.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Another new feature of SQL Server 2008 is an extension to the CONVERT function whereby a binary value can be converted to a string representation.

    e.g.

    SELECT CONVERT(VARCHAR(MAX), 0x484920544845524521, 1)

    ...returns:

    0x484920544845524521

    This removes the need for a 'BinToHexStr' function.

    Chris

  • Nice article and very helpful for 2000 and 2005. As mentioned the capability to script data is now part of SSMS 2008.

  • Chris Howarth (7/1/2009)


    Another new feature of SQL Server 2008 is an extension to the CONVERT function whereby a binary value can be converted to a string representation.

    e.g.

    SELECT CONVERT(VARCHAR(MAX), 0x484920544845524521, 1)

    ...returns:

    0x484920544845524521

    This removes the need for a 'BinToHexStr' function.

    Chris

    Thank you very much for pointing it out Chris. I wish that this extension to the CONVERT function was available in SQL Server 2005, but it is not. For example, running the following query:

    use AdventureWorks;

    go

    select isnull(convert(varchar(max), [Document], 1), 'null') blob from Production.Document;

    does not return the data in desired format (it just prints few unreadable characters per value). On the other hand the master.dbo.fn_varbintohexstr function has a very interesting limitation which I overlooked. The function was available in SQL Server 2000, but because NVARCHAR size was limited to 4000 characters, it returned null for an argument longer than 1999 characters in length. Since the 2005 version returns NVARCHAR(max), I assumed that this limitation was removed. Unfortunately, it was not.

    Running

    sp_helptext 'master.dbo.fn_varbintohexstr'

    shows that calling master.dbo.fn_varbintohexstr passing @pbinin varbinary(max) as a parameter delegates the work to sys.fn_varbintohexsubstring passing 1, @pbinin, 1, 0.

    Running

    sp_helptext 'sys.fn_varbintohexsubstring'

    Show that the 2000 version limitation is still there:

    if ( ((@cbytesin * 2) + 2 > 4000) or ((@cbytesin * 2) + 2 < 1) )

    return NULL -- @cbytesin is the datalength of the varbinary argument

    This is very unfortunate. The return type was changed from nvarchar(4000) to NVARCHAR(max) but the limitation was not commented out.

    I am sure that the need to generate inserts for tables which include binary columns with data (JPEG images, songs, MS Word documents) is limited, but if someone does have to script such data then they might consider creating a copy of the sys.fn_varbintohexsubstring with aforementioned limitation on the data size removed. Removing this limitation will make the function deadly slow 🙁

    Theoretically, if performance of the script generating catalog inserts for deployment when the large binary data is present is not an issue then removing the limitation and adjusting originally posted procedure still allows the task to be accomplished.

    There is a whitepaper titled To BLOB or Not To BLOB published my Microsoft research in 2006. Its verdict stating that "objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem" helps my case I guess.

    Oleg

  • Great article. I thing it is easy to add "exclude column" functionality:

    create proc dbo.usp_generate_inserts

    (

    @table nvarchar(255),

    @excludeCol varchar(2048)

    )

    as

    begin

    set @excludeCol = ',' + @excludeCol + ','

    ...

    where

    table_name = substring(@table, charindex('.', @table) + 1, len(@table))

    and data_type != 'timestamp'

    and not(@excludeCol like'%,' + column_name + ',%')

    order by ordinal_position;

    and simply execute by:

    exec usp_generate_inserts 'dbo.tableName', 'col1,col2,...'

    I've tried to execute this, but an error occurred for a type float :

    Msg 206, Level 16, State 2, Line 1

    Operand type clash: varbinary is incompatible with float

  • There is also an MS app. that some may be interested in to do importing/exporting: Microsoft SQL Server Database Publishing Wizard

    It can create scripts that are just data, just schema, or both.

    http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

  • Very good article. This technique could be very useful to me right now.

    I tried it on a relatively small, simple table and it worked fine. However, on a larger table with a Text field (yes, they are still out there) I encountered some problems. Firstly the file was greatly inflated with white space and had paragraph marks in the insert statement, which threw errors. Having weeded all that out, I now get the error: Operand type clash: varbinary is incompatible with text.

  • Very good article. This technique could be very useful to me right now.

    I tried it on a relatively small, simple table and it worked fine. However, on a larger table with a Text field (yes, they are still out there) I encountered some problems. Firstly the file was greatly inflated with white space and had paragraph marks in the insert statement, which threw errors. Having weeded all that out, I now get the error: Operand type clash: varbinary is incompatible with text.

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

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