Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Generating Insert Statements Expand / Collapse
Author
Message
Posted Wednesday, July 1, 2009 10:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 11:58 AM
Points: 1,676, Visits: 1,758
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



Post #745565
Posted Wednesday, July 1, 2009 10:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 17, 2011 9:01 AM
Points: 40, Visits: 41
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
Post #745593
Posted Wednesday, July 1, 2009 10:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:46 PM
Points: 79, Visits: 1,331
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
Post #745604
Posted Wednesday, July 1, 2009 11:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 12:11 PM
Points: 16, Visits: 42
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.
Post #745645
Posted Wednesday, July 1, 2009 11:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 12:11 PM
Points: 16, Visits: 42
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.
Post #745646
Posted Wednesday, July 1, 2009 12:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 40, Visits: 465
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


SSMS's built-in data scripting functionality may not be a viable alternative because it is not very customizable and modular enough to be used in an application.
Post #745685
Posted Wednesday, July 1, 2009 12:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 40, Visits: 465

...

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.

...

Oleg


Looks like they removed this limitation in SQL 2008. Hurray!!
--the output limit for nvarchar(max) is 2147483648 (2^31) bytes, that is 1073741824 (2^30) unicode characters  
if ( ((@cbytesin * 2) + 2 > 1073741824) or ((@cbytesin * 2) + 2 < 1) or ( @cbytesin is null ))
return NULL

Post #745697
Posted Wednesday, July 1, 2009 12:52 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 11:58 AM
Points: 1,676, Visits: 1,758
TSycamore (7/1/2009)
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.


The text data type is handled. It is cast as varchar(max) to address the varbinary incompatibility with text. This cast maybe not enough if your data has non-ansi values, so cast to nvarchar(max) could be used instead. I tweaked the proc to cast text as nvarchar and so far found no issues:
use AdventureWorks;
go

set nocount on;
go

create table dbo.test (col1 int not null, col2 text);
insert into dbo.test (col1, col2) values (1, 'sample 1,sample 1');
insert into dbo.test (col1, col2) values (2, 'sample 2,sample 2');
go

exec dbo.usp_generate_inserts 'dbo.test';
go

set nocount off;
go

Don't forget to drop the table. Amazingly when I include the statement to drop the table into the code block I cannot preview or submit my post, get the IE cannot display the page error.

Results:

insert into dbo.test ([col1], [col2]) values (0x00000001, 0x730061006d0070006c006500200031002c00730061006d0070006c00650020003100);
insert into dbo.test ([col1], [col2]) values (0x00000002, 0x730061006d0070006c006500200032002c00730061006d0070006c00650020003200);

As you can see from above, casting text as nvarchar makes 2 bytes per letter. For example, letter s has a code of 115, which is represented in hex as 7300. 115 is hex 73 (7 * 16 + 3) and zero is 00.

I discussed the problem and possible solution to the issue with large data values few posts above this one (on the same page, see my reply to Chris). Another thing I want to mention is the size of the data that SQL Server Management Studio displays in the query results window. Defaults are pretty small, so you might consider tweaking them to allocate enough space to fit the results. I mean adjusting Tools - Options - Query Results - SQL Server - Results to Grid - Maximum Characters Retrrieved - Non XML data and
Tools - Options - Query Results - SQL Server - Results to Text - Maximum number of characters displayed in each column settings of the Management Studio.

Oleg
Post #745701
Posted Wednesday, July 1, 2009 2:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 12:11 PM
Points: 16, Visits: 42
Oleg,

Interesting...

What I am getting when I run your example of handling a text data type is:

insert into dbo.test ([col1], [col2]) values (0x00000001, 0x73616d706c6520312c73616d706c652031);
insert into dbo.test ([col1], [col2]) values (0x00000002, 0x73616d706c6520322c73616d706c652032);

When I try to run these inserts into dbo.test, I get:

Operand type clash: varbinary is incompatible with text

Terry
Post #745736
Posted Wednesday, July 1, 2009 3:06 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 11:58 AM
Points: 1,676, Visits: 1,758
TSycamore (7/1/2009)
Oleg,

Interesting...

What I am getting when I run your example of handling a text data type is:

insert into dbo.test ([col1], [col2]) values (0x00000001, 0x73616d706c6520312c73616d706c652031);
insert into dbo.test ([col1], [col2]) values (0x00000002, 0x73616d706c6520322c73616d706c652032);

When I try to run these inserts into dbo.test, I get:

Operand type clash: varbinary is incompatible with text

Terry


Thank you very much Terry. I used the AdventureWorks database when I worked on the script, which does not have any tables with text columns. I tested the generation of the insert statements against other database which had tables with text columns but this was as far as I went. Here is one workaround I can suggest. It is admittedly silly, but it works. The script below assumes that we are where we were left off: the table dbo.test is created, but generated insert statements fail miserably.

Here is the script, but please replace the occurences of the word alterz with alter. If I spell them out correctly in the code block then I cannot submit the post

set nocount on;
go

delete dbo.test;
go

-- this does not work, there is a good reason why Microsoft recommends
-- not to use the text data type, but switch to varchar(max) instead
insert into dbo.test ([col1], [col2]) values (0x00000001, 0x73616d706c6520312c73616d706c652031);
insert into dbo.test ([col1], [col2]) values (0x00000002, 0x73616d706c6520322c73616d706c652032);
go

alterz table dbo.test alter column col2 varchar(max);
go

-- this works
insert into dbo.test ([col1], [col2]) values (0x00000001, 0x73616d706c6520312c73616d706c652031);
insert into dbo.test ([col1], [col2]) values (0x00000002, 0x73616d706c6520322c73616d706c652032);
go

-- now revert the table back to what it was
alterz table dbo.test alter column col2 text;
go

select * from dbo.test;
go

set nocount off;
go

Here is the dump from results window:
Msg 206, Level 16, State 2, Line 4
Operand type clash: varbinary is incompatible with text
col1 col2
-----------------------------
1 sample 1,sample 1
2 sample 2,sample 2

The idea is to temporarily alter the suspect table changing the text columns to varchar(max) and then reverting the change back to what it was. Though I would seriously consider permanent altering of the tables with text columns to replace those with varchar(max) or nvarchar(max). Hope this helps.

Oleg
Post #745782
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse