asked on

Update column value with a BLOB

I have the following table in a MS SQL DB:

CREATE TABLE [dbo].[Forms](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FormNumber] [int] NOT NULL,
	[Blob] [varbinary](max) NULL,
	[FileName] [nvarchar](1000) NULL,
	[FormName] [nvarchar](1000) NULL,
 CONSTRAINT [PK_Forms] PRIMARY KEY CLUSTERED 
(	[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window

The contents of the table look like this:

1	19000	 Form19000.pdf	Order Form 1
2	19001	 Form19001.pdf	Order Form 2
3	19002	 Form19002.pdf	Order Form 3

Open in new window

I have information in all the columns except the BLOB column. I would like to load the blob of a file for each row with its corresponding file. How would I do this for a table that has several hundred rows using T-SQL?

I know that I can manually load each row with the following statement but how would I do this for the whole table:

UPDATE dbo.Forms
SET Blob =
	(Select * from OPENROWSET(bulk 'C:\Forms\Form19000.pdf', single_blob) as a)
WHERE Id = 1

Open in new window

Scott Pletcher

It's difficult because SQL doesn't allow a variable in OPENROWSET.

For only several hundred rows, I would probably just use a cursor and dynamic SQL to scroll thru the source table and load each blob separately.

ste5an

One by one. Cause OPENROWSET requires that the file name is a literal, not an expression nor a variable. Thus you can only use dynamic SQL or you use an external script to load the data.

Member_2_4488436

ASKER

Could you provide an example of how that would be done with dynamic sql.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_4488436

ASKER

Thanks ste5an, great solution. I had to figure out how to make a small change so that the "where id = 1" is dynamic