Insert Binary Data from a temp table

Question

question

technette asked Feb 9, '12

Insert Binary Data from a temp table

I am trying to insert text from a temp table into a table that holds binary data. The columns..

 BITS LONGVAR
 BITS_LENGTH INTEGER

I'm using the following to insert the BITS information
BITS=CAST(CAST([MyTEXT] AS VARCHAR(8000)) AS VARBINARY(8000)) How do I calculate the BITS_LENGETH? I tried the following but it won't execute: INSERT INTO [Database].[dbo].[BINARY_TABLE] ([IDnum] ,[LineNum] ,[TYPE] ,[BITS] ,[BITS_LENGTH]) SELECT LTRIM(RTRIM([IDno])) AS IDnum ,[Line] ,'H' ,cast(cast([MyText] as varchar(max)) as varbinary(max)) ,DATALENGTH([MyText]) FROM [Database].[dbo].[TEMP_TABLE]

binary

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Feb 09, 2012 at 11:08 PM

@technette Are you sure you don't encounter any data loss with this approach? If the temp table holds the TEXT data type then it surely can be more than 8K in length and therefore, casting to MAX would be a safer bet, i.e.

BITS = cast(cast([MyText] as varchar(max)) as varbinary(max))

The datalength applied to the above will work fine with any input because the maximum size of data which can be stored in the single record single column is 2 GB anyway (unless the data is stored in the column decorated with filestream attribute), so it is guaranteed to fit into integer.

3 ·

technette commented · Feb 10, 2012 at 07:04 PM

Thank you Oleg! When I select existing rows the BITS column data is in Hexidecimal format. The program is designed to read and save data from a text editor that pops up. I have to upload text data into many rows. Since the BITS_LENGTH Int column cannot be null I will try to do the following while I am inserting the text: SELECT BITS_LENGTH = DATALENGTH(@BITS) Is this the correct syntax?

0 ·

Answer 1 · 2012-02-09T18:03:38Z

Pavel Pawlowski answered Feb 9, '12

To get number of bytes you can use **`DATALENGTH`** function. See MSDN [DATALENGTH (Transact-SQL)][1] for more details. [1]: http://msdn.microsoft.com/en-us/library/ms173486.aspx

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

technette commented · Feb 10, 2012 at 07:08 PM

Thank you Pavel. I saw your note after I posted a response to Oleg.

0 ·

question