Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Inserting Binary Data

There are a number of articles on dealing with the insertion of binary data into SQL Server using BULK INSERT, OPENROWSET, and even Java. However I saw someone asking recently how to insert data dynamically and thought that was an interesting question.

If you are working with some front end language, this is easy with the various ODBC/OLEDB/ADO/etc interfaces into SQL Server. These languages allow you to specify parameters as a binary stream.

However if you need to insert this data in T-SQL, what can you do? I know that there is a CAST function and that should work with varbinary. Let’s test something:

CREATE TABLE Binarytest
( id INT
, note VARBINARY(500)
)
;
INSERT BinaryTest SELECT 1, CAST( 'A' AS VARBINARY)

SELECT note, CAST( note AS VARCHAR) FROM BinaryTest

This returns:

binary1

I also know that I can directly take binary data in SQL Server and work with it, as long as I specify it’s binary. So I can do this:

INSERT BinaryTest SELECT 1, 0x41

If I now query my table, I get:

binary2

Not the easiest format to work with, but if I construct binary data somehow, I can work with it in T-SQL if I need it.


Filed under: Blog Tagged: syndicated, T-SQL

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...