SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generating Insert Statements


Generating Insert Statements

Author
Message
Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2201 Visits: 1817
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 Sad

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
olimp23
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 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

Adam Gojdas
Adam Gojdas
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 1450
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
TSycamore
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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.
TSycamore
TSycamore
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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.
Langston Montgomery
Langston Montgomery
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 508
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.
Langston Montgomery
Langston Montgomery
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 508

...

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


Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2201 Visits: 1817
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
TSycamore
TSycamore
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2201 Visits: 1817
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 Sad


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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search