Generating Insert Statements

  • Oleg Netchaev

    SSCertifiable

    Points: 5268

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

  • Ola L Martins-329921

    Mr or Mrs. 500

    Points: 522

    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...

  • Chris Howarth-536003

    SSCommitted

    Points: 1775

    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

  • sample1

    Ten Centuries

    Points: 1187

    It is an amazing idea.

  • Jeff Paulson

    Grasshopper

    Points: 19

    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.

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Nice article 🙂 and really well written.

    Same idea as mine:

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

  • chudman

    SSCrazy

    Points: 2369

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

    Thank you, Oleg.

    Jeff Bennett

    SQL DBA

    Saint Louis, MO

  • WayneS

    SSC Guru

    Points: 95341

    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

  • Chris Howarth-536003

    SSCommitted

    Points: 1775

    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

  • Jack Corbett

    SSC Guru

    Points: 184360

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


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck out these links on how to get faster and more accurate answers: Forum Etiquette: How to post data/code on a forum to get the best helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Oleg Netchaev

    SSCertifiable

    Points: 5268

    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

  • olimp23

    Valued Member

    Points: 54

    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

  • Adam Gojdas

    SSCommitted

    Points: 1766

    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

  • TSycamore

    SSC Journeyman

    Points: 78

    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.

  • TSycamore

    SSC Journeyman

    Points: 78

    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 59 total)

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