Problem inserting binary data through SQL query (SQL Server 2008)

Question

These is the query i have found for inserting binary data (using varbinary) which is working perfectly CREATE TABLE Employees ( Id int, Name varchar(50) not null, Photo varbinary(max) not null )

INSERT INTO Employees (Id, Name, Photo) SELECT 10, 'John', BulkColumn from Openrowset( Bulk 'C:\photo.bmp', Single_Blob) as EmployeePicture

But what if the table employee has columns like:
CREATE TABLE Employees ( Id int, Photo varbinary(max) not null, Name varchar(50) not null )

[Photo column is 2nd over here!]
I tried manipulating the insert query but didnt help!!

Show us what queries you tried. And did you try just using the query as-is? Since you are using the variant of INSERT INTO that lists column names, it shouldn't matter what order the columns are in vs. their order in the actual table -- that's the point of using column names -- not having to worry about their order in the table. — QuantumMechanic
– QuantumMechanic, Commented Apr 5, 2011 at 16:12

Martin Smith · Accepted Answer · 2011-04-05 16:50:15Z

2

You would use

 INSERT INTO Employees
 SELECT 10, BulkColumn AS EmployeePicture, 'John'
 FROM OPENROWSET( BULK 'C:\photo.bmp', Single_Blob) bc

Or

 INSERT INTO Employees
 SELECT 10, 
        (SELECT BulkColumn AS EmployeePicture  FROM OPENROWSET( BULK 'C:\photo.bmp', Single_Blob) bc), 
        'John'

I presume you were probably trying something like this

 INSERT INTO Employees
 SELECT 10, 
        BulkColumn AS EmployeePicture  FROM OPENROWSET( BULK 'C:\photo.bmp', Single_Blob), 
        'John'

answered Apr 5, 2011 at 16:50

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user646093 Over a year ago

You are right Martin!! This is one of the 7 to 8 queries i tried. :) I would be using the second query as it is logically sound and multiple binary data can be easily inserted through it. Are there any online resources where i can study and understand the syntax of these queries? I know the basics of SQL which are provided on W3Schools but complex queries such as these are hard to find. BTW which formatting did u use for displaying queries?

Martin Smith Over a year ago

To format the query as code in StackOverflow you select the text to be formatted as code and hit the code icon {}. Not really sure about online resources. Joe Celko's SQL for Smarties is definitely worth a read though.

user646093 Over a year ago

Thanks again Martin. I got the book, will definitely gain something from it.

Collectives™ on Stack Overflow

Problem inserting binary data through SQL query (SQL Server 2008)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related