BULK INSERT COMMAND: Using both a variable and a '\n' row terminator

Question

Post reply

BULK INSERT COMMAND: Using both a variable and a '\n' row terminator

huston.dunlap

SSCommitted

Points: 1551
More actions
December 3, 2009 at 6:09 pm

#156468

I want to write a bulk insert statement that utilizes a variable:
Bulk Insert #TABLE
FROM @SOURCE
WITH (ROWTERMINATOR = '')
I know the common way to do this is to use the EXEC command, which I wrote as:
SET @RUNNER =
'Bulk Insert #TABLE
FROM' + @SOURCE +
'WITH (ROWTERMINATOR = '''')'
EXEC(@RUNNER)
However, I'm getting a consistent error around the rowterminator. The EXEC command doesn't seem to like it, and my attempts to reformat it have failed.
Is there a way to use a variable and this row terminator?

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

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

dmoldovan SSCertifiable Points: 6334 More actions · Answer 1

Don't forget to add the necessary blanks around FROM and WITH:

ET @RUNNER =

'Bulk Insert #Table

FROM ' + @SOURCE +

' WITH (ROWTERMINATOR = '''')'

For the row terminator - see BOL--->Specifying Field and Row Terminators.

purushotham.k9 SSCommitted Points: 1992 More actions · Answer 2

This shouldn't give any erros. you can use this

BULK INSERT <Table Name>

FROM <File Location> (Full path)

WITH

(

FIELDTERMINATOR ='|',

ROWTERMINATOR = ''

)

huston.dunlap SSCommitted Points: 1551 More actions · Answer 3

dmoldovan (12/4/2009)
Don't forget to add the necessary blanks around FROM and WITH:
ET @RUNNER =
'Bulk Insert #Table
FROM ' + @SOURCE +
' WITH (ROWTERMINATOR = '''')'
For the row terminator - see BOL--->Specifying Field and Row Terminators.

Still getting errors on this one.

One thing I am doing is using a UNC path for my filepath. Aka \\server\xxxxx

Bulk Insert has never had a problem with it, is there an issue when used in an EXEC statement?

dmoldovan SSCertifiable Points: 6334 More actions · Answer 4

dmoldovan

SSCertifiable

Points: 6334

December 4, 2009 at 12:27 pm

#1087966

Can you post the error message?

huston.dunlap SSCommitted Points: 1551 More actions · Answer 5

dmoldovan (12/4/2009)
Can you post the error message?

MSG 102, LEVEL 15, STATE 1, Line 1

Incorrect syntax near '\'.

I should mention I have also tried escape all the slashes with [] and '' but neither seemed to work.

huston.dunlap SSCommitted Points: 1551 More actions · Answer 6

I FIGURED IT OUT!

The problem wasn't the slashes....the problem was a bulk insert statement requires its path put in quotes.

when I did this (set @var = '\\stuff')....I was providing the command \\stuff......not '\\stuff'.

Once I changed it to set @var = '''\\stuff''' it worked like a charm.

Thanks for everyone's help, this one was maddening.

dmoldovan SSCertifiable Points: 6334 More actions · Answer 7

Try something like

SET @source = '''\\server\xxxxx'''

purushotham.k9 SSCommitted Points: 1992 More actions · Answer 8

i got your problem....The server should be FTP and Folder should be shared...

If you get a chance place the error message..it would helpful to debug...

BULK INSERT COMMAND: Using both a variable and a '\n' row terminator

Cookies on SQLServerCentral