CODEWITHSUNDEEP
sql-server-2008insertbinary-datavarbinarymax
user646093
user646093

Reputation: 1603

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

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!!

Upvotes: 2

Views: 2181

Answers (1)

Martin Smith
Martin Smith

Reputation: 453328

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'

Upvotes: 2

Related Questions