June 30, 2009 at 9:29 pm
Comments posted to this topic are about the item Generating Insert Statements
July 1, 2009 at 2:21 am
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...
July 1, 2009 at 2:46 am
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
July 1, 2009 at 4:45 am
It is an amazing idea.
July 1, 2009 at 5:42 am
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.
July 1, 2009 at 6:49 am
Nice article 🙂 and really well written.
Same idea as mine:
July 1, 2009 at 6:56 am
Brilliant!!! Outside-the-box thinking that turns conventional processes on their head.
Thank you, Oleg.
Jeff Bennett
SQL DBA
Saint Louis, MO
July 1, 2009 at 7:01 am
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
July 1, 2009 at 7:32 am
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
July 1, 2009 at 8:56 am
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
Check 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 help
Need an Answer? Actually, No ... You Need a Question
July 1, 2009 at 10:02 am
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
July 1, 2009 at 10:27 am
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
July 1, 2009 at 10:42 am
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.
July 1, 2009 at 11:24 am
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.
July 1, 2009 at 11:26 am
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