CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Eric Ton
Eric Ton

Reputation: 1

Insert images in two columns in table SQL Server

I have a table with 4 columns like this:

CREATE TABLE SaveFiles
(
    FileID INT,
    Name NVARCHAR(50) NOT NULL,
    Image1 VARBINARY(MAX) NOT NULL,
    Image2 VARBINARY(MAX) NOT NULL,
)

I am trying to insert images into image1 and image2 columns.

I am able to insert only one image in image1 column, I tried to insert image2 column as well at a time in a table. I have used below query to insert image in image1, but my target is to insert image2 as well.

INSERT INTO [dbo].[SaveFiles] ([FileID], Name, Files)
    SELECT  
        1, 'Home Page 2', 
        BulkColumn 
    FROM 
        OPENROWSET(BULK N'D:\LOGOS\Home Page.png', SINGLE_BLOB) image;

I tried to insert two columns as below. But am not able to insert, please help me to insert 4 columns, 1 int, 2 nvarchar, VARBINARY, VARBINARY into table.

INSERT INTO [dbo].[SaveFiles] ([FileID], Name, Files)
    SELECT  
        1, 'Home Page 2', 
        BulkColumn 
    FROM 
        OPENROWSET(BULK N'D:\LOGOS\Home Page.png', SINGLE_BLOB) image,
        BulkColumn 
    FROM OPENROWSET(BULK N'D:\LOGOS\Home Page.png', SINGLE_BLOB) image;

Thanks in advance.

Upvotes: 0

Views: 1031

Answers (1)

Vinit
Vinit

Reputation: 2607

If you are trying to insert same image in 2 columns then you can try this query. You just need to use OPENROWSET once.

INSERT INTO [dbo].[SaveFiles] ([FileID], Name, Image1, Image2) 
SELECT 
  1, 
  'Home Page 2', 
  image.BulkColumn ,
  image.BulkColumn 
FROM OPENROWSET(BULK N'D:\LOGOS\Home Page.png', SINGLE_BLOB) image

If you are trying to insert 2 different images then, use OPENROWSET twice as below.

INSERT INTO [dbo].[SaveFiles] ([FileID], Name, Image1, Image2) 
    SELECT 
      1, 
      'Home Page 2', 
      (Select BulkColumn FROM OPENROWSET(BULK N'D:\LOGOS\Home Page1.png', SINGLE_BLOB) as img1) ,
      (Select BulkColumn FROM OPENROWSET(BULK N'D:\LOGOS\Home Page2.png', SINGLE_BLOB)  as img2)

Upvotes: 1

Related Questions