Bulk Insert/Concatenating a Variable

[SQLJoe]

My code below is throwing errors (at bottom) but I don't know what the problem is. I'm just passing this procedure a string to concatenate onto the end of the directory in the FROM clause. Before I added the concatenation, the syntax checked out fine. I'm sure it's a pretty basic mistake. I don't do bulk inserts very often. Any help is appreciated.

create procedure spWPLoadDataFile24(@FileName varchar(200))

as

BULK
INSERT tblWPdatafile24_Staging
FROM '\\Gmc0190\data\WelcomePacketData\cpnom\' + @FileName
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO


Msg 102, Level 15, State 1, Procedure spWPLoadDataFile24, Line 8
Incorrect syntax near '+'.

Msg 319, Level 15, State 1, Procedure spWPLoadDataFile24, Line 9
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

 

[SQLJoe]

I should also have mentioned that I'm using SQL 2K8. If there's a better/easier way to upload data from a pipe delimited text file, please let me know.

 

[SQLJoe]

I also should have mentioned that I don't really have time to do this in SSIS yet. I know that would be ideal, and I hope to make that change soon.

 

[gmmastros]

Try this:

Alter procedure spWPLoadDataFile24(@FileName varchar(200))

as
Declare @SQL VarChar(8000)

Set @SQL = '
BULK
INSERT tblWPdatafile24_Staging
FROM ''\\Gmc0190\data\WelcomePacketData\cpnom\' + @FileName + '''
WITH
(
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''\n''
)'
Exec (@SQL)

If there's a better/easier way to upload data from a pipe delimited text file, please let me know.

There are a lot of ways to get data in to SQL server. A friend of mine wrote this blog a while ago.

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/title-12

better/easier is certainly subjective. Truth be told, this is the method that I prefer (although I don't have a need to bulk insert very often).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLJoe]

That worked beautifully, thanks! Out of curiosity, do you know why my method didn't work?

Also, it's interesting that you prefer this method over SSIS packages (if I understood you correctly). May I ask why?

 

[gmmastros]

Your method didn't work because the Bulk Insert command doesn't allow string concatenation (or even a variable file name). So... you create the entire command as a string and execute it dynamically.

I very rarely import lots of data in to by database. I am the owner of a software company with a niche product. When I sell my software, I gather information from my customer and load it in to the database. I then install the DB and software on their system. Since I cannot always control the SQL Server that the database is attached to, I try to ONLY use functionality that ALWAYS works. You need special database permissions for BULK INSERT, so I don't have any of that in my production code. Using SSIS in a remote environment is also problematic. I jump through a ton of extra hoops to make my application rock solid. Anything that could possibly fail has been taken out.

If I had a lot of data import/export tasks that I needed to run on a regular basis, I would probably become a big fan of SSIS.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom