Monday, March 26, 2012

insert with exec sql server 2005

Hi,
This is a query that inserts the xmlcontents of a file into the table.

insert into
tbTrades
(
xmlContents
)
Exec ('SELECT Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.FilePath + ''', SINGLE_CLOB) as D')

Now I would like to add an extra field in the insert. something like:

declare @.FileName varchar(200)

set @.FileName = 'c:\1234.xml'

insert into
tbTrades
(
FileName,
xmlContents
)
@.FileName,
Exec ('SELECT Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.FilePath + ''', SINGLE_CLOB) as D')

This gives an error:
Incorrect syntax near '@.FileName'.

p.s. I am happy with the first query, just would like to get the second one to work too.
Thanks

how about:

Code Snippet

insert into

tbTrades

(

FileName,

xmlContents

)

Exec ('SELECT ''' + @.FileName + ''', Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.FilePath + ''', SINGLE_CLOB) as D')

No comments:

Post a Comment