insert with exec - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

insert with exec

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-07-12 : 10:37:10

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

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-12 : 10:41:28

quote:
Originally posted by arkiboys
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')

Need an SQLDB consultant?
check www.veeningsengineering.nl

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-12 : 10:44:41

try this one, else i wan't to know at wich line you get your error.

quote:
Originally posted by arkiboys
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')

Need an SQLDB consultant?
check www.veeningsengineering.nl

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-12 : 11:05:56

Try

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

Madhivanan

Failing to plan is Planning to fail

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-07-12 : 12:00:41

Solved. thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-12 : 23:39:24

More on dynamic sql
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources