July 1, 2009 at 12:24 pm
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.
July 1, 2009 at 12:45 pm
...
Show that the 2000 version limitation is still there:
if ( ((@cbytesin * 2) + 2 > 4000) or ((@cbytesin * 2) + 2 1073741824) or ((@cbytesin * 2) + 2 < 1) or ( @cbytesin is null ))
return NULL
July 1, 2009 at 12:52 pm
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
July 1, 2009 at 2:03 pm
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
July 1, 2009 at 3:06 pm
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
July 1, 2009 at 6:38 pm
The insert-generating method described above is clearly error prone.
Well, yes - but producing binary inserts clearly loses some of the flexibility I often use a scripting procedure for in the first place. This looks quite suited to moving bulk test data across if you want a deployment script (which I guess is pretty much what it is intended for) but more often than not I want a procedure you could add a condition to, and perhaps edit the output, just to transfer selected records. When I wrote about this previously, I felt that many didn't really get the point that you can use insert statement generators for different purposes than just scripting the contents of an entire table.
Also, not that it probably matters too much in practice, but that function call has a bit of a performance hit - that's what makes it take about 30 to 50 times as long as the insert generation script I use. I tried getting the CONVERT approach to work in SQL Server 2005, but the few variations I tried seemed to indicate that casting varbinary to varchar didn't work - just seemed to return blanks.
Interesting idea though. I lament somewhat the deprecation of DTS for SSIS, which seems like a sledgehammer to crack a nut for much of what I want to do day-to-day, and this could be useful for things like setting up a test environments for databases that aren't too big.
July 1, 2009 at 6:45 pm
Nice article!
When Possible, I will add IF EXISTS and UPDATE sentences and post it here
July 1, 2009 at 8:01 pm
Tony Webster (7/1/2009)
Also, not that it probably matters too much in practice, but that function call has a bit of a performance hit - that's what makes it take about 30 to 50 times as long as the insert generation script I use. I tried getting the CONVERT approach to work in SQL Server 2005, but the few variations I tried seemed to indicate that casting varbinary to varchar didn't work - just seemed to return blanks.
Tony,
Yes, that function call has a huge performance hit. This is easily understandable though because T-SQL is not the language one wants to use to buffer the strings. Removing the 1,999 characters restriction will make it even slower. But the bottom line is that if you have a table in the development database similar to the one of Production.Document of AdventureWorks and you do need to submit a script creating this table as well as the insert statements for its records because creating SSIS package for exporting data from development to say QA is not an option then the procedure I described can make it happen. There are faster scripts, but they do not necessarily include generation of insert statements for varbinary(max) columns.
The situation with CONVERT is the following: extended version of it in SQL Server 2008 allows conversion of binary values to varchar(max). I replied earlier to Chris Howarth who wrote:
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.
This is great that extended function like this exists in 2008. It makes me wish I could switch to SQL Server 2008 today, but this is not my call. In 2005 version it does not return 0x484920544845524521 though. It returns HI THERE!, which is what one would expect it to return. Calling CONVERT(varchar(max), YOUR_BINARY_COLUMN, 1) in 2005 version will return few unreadable characters, so it cannot be used directly.
Yes, my script maybe slow for wide data, but it does not seem to be too bad for reasonably narrow tables even if they have a big number of records. It can be easily adjusted to include the predicates in order to generate inserts for a subset of existing data.
Oleg
July 2, 2009 at 3:40 am
There is a minor bug in the procedure.
Input parameter is 'schema.Table' but the schema part is not used when selecting from information_schema.columns. It doesn't matter if you don't have the same table name in multiple schemas (as we sometimes do).
I modified that section as:
...
from
information_schema.columns
where
table_schema = left(@table, charindex('.', @table) - 1 )
and table_name = substring(@table, charindex('.', @table) + 1, len(@table))
...
and then it worked as intended.
July 2, 2009 at 3:53 am
I have found more better way to do this as mention below
set quoted_identifier off
declare @sql1 nvarchar(1000)
set @sql1="select 'insert county (county_fips, state_fips) values(''' + county_fips + ''',''' + state_fips + ''')' from county"
print @sql1
exec sp_executesql @sql1;
=====================================================
Instead of using lots of ''''''''''''''' and replace function above can be used...
Imran.
July 2, 2009 at 4:00 am
Scripting in binary format is a great idea, however the script is failing for some reason with this table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EcsPurAccount](
[id] [int] NOT NULL,
[reference] [varchar](12) NOT NULL,
[name] [varchar](45) NULL,
[address_1] [varchar](45) NULL,
[address_2] [varchar](45) NULL,
[address_3] [varchar](45) NULL,
[address_4] [varchar](45) NULL,
[address_5] [varchar](45) NULL,
[postcode] [varchar](11) NULL,
[telephone_1] [varchar](21) NULL,
[telephone_4] [varchar](21) NULL,
[email_1] [varchar](71) NULL,
[vatnumber] [varchar](21) NULL,
[days_pay_2] [int] NULL,
[co_pref] [varchar](5) NULL,
[locked_1] [int] NULL,
[locked_2] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
The result is this:
insert into dbo.ecspuraccount ([id], [reference], [name], [address_1], [address_2], [address_3], [address_4], [address_5], [postcode], [telephone_1], [telephone_4], [email_1], [vatnumber], [days_pay_2], [co_pref], [locked_1], [locked_2]) values (0x00000001
insert into dbo.ecspuraccount ([id], [reference], [name], [address_1], [address_2], [address_3], [address_4], [address_5], [postcode], [telephone_1], [telephone_4], [email_1], [vatnumber], [days_pay_2], [co_pref], [locked_1], [locked_2]) values (0x00000002
insert into dbo.ecspuraccount ([id], [reference], [name], [address_1], [address_2], [address_3], [address_4], [address_5], [postcode], [telephone_1], [telephone_4], [email_1], [vatnumber], [days_pay_2], [co_pref], [locked_1], [locked_2]) values (0x00000003
insert into dbo.ecspuraccount ([id], [reference], [name], [address_1], [address_2], [address_3], [address_4], [address_5], [postcode], [telephone_1], [telephone_4], [email_1], [vatnumber], [days_pay_2], [co_pref], [locked_1], [locked_2]) values (0x00000004
Presumably some variable is not big enough. This was scripting to a file by the way. Had a quick look but can't see how this could happen. Any bright ideas!?
Matt
July 2, 2009 at 9:02 am
wilkimatt (7/2/2009)
Scripting in binary format is a great idea, however the script is failing for some reason with this table:.....................
The result is this:
insert into dbo.ecspuraccount ([id], [reference], [name], [address_1], [address_2], [address_3], [address_4], [address_5], [postcode], [telephone_1], [telephone_4], [email_1], [vatnumber], [days_pay_2], [co_pref], [locked_1], [locked_2]) values (0x00000001
...............................
Presumably some variable is not big enough. This was scripting to a file by the way. Had a quick look but can't see how this could happen. Any bright ideas!?
Matt
Matt,
The script does not fail, but the settings of your editor window are too small to display the records in their entirety. For example, out of the box Management Studio only displays 1024 characters per column if you execute the query with results to text option. Because every insert statement is a single record with single column, the record is truncated. In one of my replies yesterday (the one on page 2 of this forum) I mentioned the necessity to adjust the query results if needed. The script to file option borrows the results to text settings. This means that the easiest way is to Ctrl+D (results to grid), execute the proc, click the results header, Ctrl+C, notepad (or whatever text editor), Ctrl+V. Hope this helps.
Oleg
July 2, 2009 at 9:12 am
Hi there,
Nice article, I have one question. When I change your sample code from
use AdventureWorks;
......
declare @t table (col1 int not null, col2 varchar(30));
insert into @t (col1, col2)
values
(
......
TO
use AdventureWorks;
......
declare @t table (col1 float not null, col2 varchar(30));
insert into @t (col1, col2)
values
(
......
and ran the statement it raised an error
Operand type clash: varbinary is incompatible with float
Any help will be appreciated.
Regards,
a_k93
July 2, 2009 at 9:30 am
imran.raza (7/2/2009)
I have found more better way to do this as mention belowset quoted_identifier off
declare @sql1 nvarchar(1000)
set @sql1="select 'insert county (county_fips, state_fips) values(''' + county_fips + ''',''' + state_fips + ''')' from county"
print @sql1
exec sp_executesql @sql1;
=====================================================
Instead of using lots of ''''''''''''''' and replace function above can be used...
Imran.
Imran,
I am sorry but you did not find a better way. In the very beginning of the article I mentioned about the challenges associated with a straightforward implementation of the insert generating script, similar to the one of yours. These include conversion issues and single quotes/nulls handling. Let's consider the following script (your script but I will also create the table first):
use AdventureWorks;
go
-- create table
create table dbo.county (county_fips varchar(50) null, state_fips datetime null);
-- insert some records
insert into dbo.county (county_fips, state_fips) values ('It aint''t not working though I think it should', '2009-05-30');
insert into dbo.county (county_fips, state_fips) values (null, '2009-06-30');
insert into dbo.county (county_fips, state_fips) values ('No single quotes here', null);
-- your script
set quoted_identifier off
declare @sql1 nvarchar(1000)
set @sql1="select 'insert county (county_fips, state_fips) values(''' + county_fips + ''',''' + state_fips + ''')' from county"
print @sql1;
exec sp_executesql @sql1;
The above fails like this:
select 'insert county (county_fips, state_fips) values(''' + county_fips + ''',''' + state_fips + ''')' from county
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
OK, lets make another table as simple as it can be with both columns as varchars:
-- create table
create table dbo.county2 (county_fips varchar(50) null, state_fips varchar(50) null);
-- insert some records
insert into dbo.county2 (county_fips, state_fips) values ('It aint''t working though I think it should', 'a');
insert into dbo.county2 (county_fips, state_fips) values (null, 'b');
insert into dbo.county2 (county_fips, state_fips) values ('No single quotes here', null);
-- your script
set quoted_identifier off
declare @sql1 nvarchar(1000)
set @sql1="select 'insert county2 (county_fips, state_fips) values(''' + county_fips + ''',''' + state_fips + ''')' from county2"
print @sql1;
exec sp_executesql @sql1;
This does not fail, but does not generate inserts either. The first record will fail to execute because it will encounter unclosed string, the other two records are simply nulls, because concatenation of null to existing value yields null by default. Here is the dump from query results window:
select 'insert county2 (county_fips, state_fips) values(''' + county_fips + ''',''' + state_fips + ''')' from county2
-----------------------------------------------------------------------------
insert county2 (county_fips, state_fips) values('It aint't working though I think it should','a')
NULL
NULL
(3 row(s) affected)
Please don't forget to drop those tables.
Oleg
July 2, 2009 at 10:24 am
a_k93 (7/2/2009)
Hi there,Nice article, I have one question. When I change your sample code from...
and ran the statement it raised an error
Operand type clash: varbinary is incompatible with float
Any help will be appreciated.
Regards,
a_k93
There are 4 data types which cannot be explicitly converted from varbinary. Those are float, real (ISO synonym for real is float(24)), text and ntext. There is a table of all possible conversion combinations on MSDN site in Remarks section. According to BOL float and real are
Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.
When float is declared without number of bits that are used to store the mantissa specified then that number defaults to 53.
This means that in this case it has 15 digits precision and occupies 8 bytes.
To make the long story short, yesterday I posted a reply to Terry Sycamore who was asking about the similar problem with text (post from me yesterday at 1:52:15 PM, on page 2 of this forum). I came up with one workaround, which is to alter the table's column from text to varchar(max), run the procedure and then alter the table's column back to text. This might not be the best way to do it but it works. Additionally, Microsoft strongly recommends to stop using text data type anyway as it will not be supported in future versions of SQL Server. In your case, you can alter the float column to its respective decimal (p, s) equivalent, run the script and then alter the column back to float.
Hope this helps.
Oleg
Viewing 15 posts - 16 through 30 (of 58 total)
You must be logged in to reply to this topic. Login to reply