Dynamic File and Path on Bulk Insert

[crystalreporting]

I have a series of folders containing PDF documents that I want to insert into a SQL table. I can get this to work for a single fixed document, but I am having trouble making the file path and file name dynamic. Here's what I have that works;

insert into Image_Table (
        Customer,
        Invoice_Number,
        Document
)
select
	Invoice_Table.Customer as Customer,
	Invoice_Table.Invoice_Number as Invoice_Number,
	(SELECT * FROM OPENROWSET(BULK N'C:\50.pdf', SINGLE_BLOB) as Document)
from
	Invoice_Table

The PDF files are stored in a separate folder for each customer, with the invoice number being the actual filename e.g invoice number 123 for customer AB1000 would be C:\AB1000\123.pdf. I'd like to replace the 'C:\50.pdf' in the BULK statement with the customer number and invoice number from the Invoice_Table table.

Thanks in advance...

Peter Shirley
Macola Consultant, PA and surrounding states.

http://www.erpessentials.com

 

[sqlprograms]

Pass the FileName as parameter and dynamically build the sql query.

declare @FileName as varchar(300)
set @FileName='C:\AB1000\123.pdf'
declare @SQLQuery as varchar(max)

set @SQLQuery='insert into Image_Table (
Customer,
Invoice_Number,
Document
)
select
Invoice_Table.Customer as Customer,
Invoice_Table.Invoice_Number as Invoice_Number,
(SELECT * FROM OPENROWSET(BULK N'''+@FileName+''', SINGLE_BLOB) as Document)
from
Invoice_Table'
execute(@SQLQuery)

SQL Server Programmers

http://www.sql-programmers.com/

 

[crystalreporting]

Thanks for the response. This gets me a step closer as I can at least control the value of the image to be inserted.

However, I need to read the image for every row found in the Invoice_Table and create a matching row (that includes the corresponding image) in the Image_Table. In the example you posted, if there are 50 rows in the Invoice_Table, I get 50 rows added to the Image_Table, but all with the same 'C:\AB1000\123.pdf' image.

Peter Shirley
Macola Consultant, PA and surrounding states.

http://www.erpessentials.com