using variables in openrowset

Question

0.00/5 (No votes)

See more:

SQL

INSERT INTO tblSmartpayFiles
    (intFKACBFileID,strFileType,strPACSNum, binFileContent)
    SELECT (select intPKAutoACBFileID from tblACBFiles where strPacsNum = @pacs),@type, @pacs,
        BulkColumn FROM OPENROWSET(
            Bulk 'd:\FilestreamData\L1DAYESHM6', SINGLE_BLOB) AS BLOB

Hi I have the above script which i used to test with,but now i need to replace the directory after the "Bulk" to a variable named @directory.

how will I go about doing this?
just adding @directory doesnt work.

SQL

Bulk @directory, SINGLE_BLOB) AS BLOB

so ultimately i want something like this.

Posted 5-Dec-12 1:45am

Member 9374423

Updated 5-Dec-12 2:07am

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ravi sundar k · Accepted Answer · 2012-12-06T06:40:00

You can do it using dynamic query like

Declare sql1 varchar(2000)

sql1 = 'INSERT INTO tblSm
artpayFiles (intFKACBFileID,strFileType,strPACSNum, binFileContent) SELECT (select intPKAutoACBFileID from tblACBFiles where strPacsNum =' + @pacs +
'),' + @type + ',' + @pacs + ', BulkColumn FROM OPENROWSET( Bulk' + @directory + ', SINGLE_BLOB) AS BLOB'

This may helps you try this and getback it there are any further issues.

using variables in openrowset

1 solution

Solution 1

Add your solution here

Preview 0

using variables in openrowset

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us