INSERT BULK execute with @variables

Question

Post reply

INSERT BULK execute with @variables

jalford12

SSC Rookie

Points: 34
More actions
May 30, 2010 at 6:49 am

#156990

I'm going round in circles on this one, and every time I think I've fixed it, then the error messages start at the beginning again...
USE marketing
GO
DELETE FROM competition
GO
-- re-declare variables, erased by GO
SET DATEFORMAT dmy
DECLARE @SQLString NVARCHAR(1000)
DECLARE @txtFile VARCHAR(400)
DECLARE @xmlFile VARCHAR(400)
DECLARE @charType VARCHAR(20)
DECLARE @filePath VARCHAR(400)
SET @filePath = 'H:\'
DECLARE @tblName VARCHAR(40)
SET @tblName = 'competition'
SET @txtFile = QUOTENAME(@filePath + @tblName + '.txt', '''')
SET @xmlFile = QUOTENAME(@filePath + @tblName + '.xml', '''')
SET @charType = QUOTENAME('nvarchar', '''')
SET IDENTITY_INSERT competition ON
SET @SQLString = 'INSERT INTO competition (Competition_ID,Competition,Title,Parent_ID) '
+ 'SELECT Competition_ID,Competition,Title,Parent_ID '
+ 'FROM OPENROWSET(BULK @txtFile,FORMATFILE = @xmlFile,CODEPAGE=RAW,FIELDTERMINATOR=''\t'',FIRSTROW=2) as t1 ;'
EXECUTE SP_EXECUTESQL @SQLString
The PRINT of the string looks fine to me, but then what do I know? 😀
INSERT INTO competition (Competition_ID,Competition,Title,Parent_ID) SELECT Competition_ID,Competition,Title,Parent_ID FROM OPENROWSET(BULK @txtFile,FORMATFILE = @xmlFile,CODEPAGE=RAW,FIELDTERMINATOR='\t',FIRSTROW=2) as t1 ;
T-SQL will NOT accept embedded variables in the BULK segment of the statement
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@txtFile'.
I've even tried some off-the-wall logic like
SET @SQLString = 'INSERT INTO competition (Competition_ID,Competition,Title,Parent_ID) '
+ 'SELECT Competition_ID,Competition,Title,Parent_ID '
+ 'FROM OPENROWSET('
+ 'BEGIN '
+ 'DECLARE @txtFile VARCHAR(400) = SET @txtFile = ' + QUOTENAME(@filePath + @tblName + '.txt', '''')
+ 'DECLARE @xmlFile VARCHAR(400) = SET @xmlFile = ' + QUOTENAME(@filePath + @tblName + '.xml', '''')
+ 'BULK @txtFile,FORMATFILE = @xmlFile,CODEPAGE=RAW,FIELDTERMINATOR=''\t'',FIRSTROW=2'
+ 'END; '
+ ') as t1 ;'
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'BEGIN'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SET'.
but that didn't work either, and it sort of defeats the object of declaring stuff in one place...
A little help here and a pointer in the right direction would be greatly appreciated! 😉

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You need to use the real file names instead of @txtFile and @xmlFile.

The following should work:

SET @SQLString = 'INSERT INTO competition (Competition_ID,Competition,Title,Parent_ID) '

+ 'SELECT Competition_ID,Competition,Title,Parent_ID '

+ 'FROM OPENROWSET(BULK ' + @txtFile +',FORMATFILE = ' + @xmlFile +',CODEPAGE=RAW,FIELDTERMINATOR=''\t'',FIRSTROW=2) as t1 ;'

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

jalford12 SSC Rookie Points: 34 More actions · Answer 2

Thank you so much.

I knew it was a case of not seeing the wood for the trees.

It's obvious when I think about it.

Thanks for the "other set of eyes"

😀

LutzM SSC Guru Points: 107049 More actions · Answer 3

You're very welcome 🙂

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]