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, Apr 05 '11 at 16:12

score 2 · Accepted Answer · answered Apr 05 '11 at 16:50

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 05 '11 at 16:50

Martin Smith

438,706
87
741
845

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](http://www.w3schools.com) but complex queries such as these are hard to find. BTW which formatting did u use for displaying queries? – user646093 Apr 06 '11 at 14:10
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. – Martin Smith Apr 06 '11 at 14:16
1

Thanks again Martin. I got the book, will definitely gain something from it. – user646093 Apr 07 '11 at 17:43

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

1 Answers1