Bulk Insert

Question

Bulk Insert

conwaydean

SSC Veteran

Points: 279
More actions
March 21, 2002 at 1:09 am

#78275

I am trying to use the bulk insert feature and it works perfectly when I hardcode the file name in the data_file parameter (e.g. 'c:\...'):
Bulk Insert InLogDataSQL FROM 'c:\logs\20020214.log'
WITH (
FIELDTERMINATOR = ' ',
FIRSTROW = 5,
ROWTERMINATOR = '\N'
)
But when I want to substitute the file name with a parameter, it causes an error. I have tried to use the exec method, and dynamically provide the string equivalent of the code above but cannot duplicate the single apostrophes.
Does anyone know how I can dynamically create the above code into a @some_string and then call:
exec(@some_string)
??
Any help would be mightily appreciated!!!

Viewing 15 posts - 1 through 14 (of 14 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

To get the single apostrophes just double them when building dynamically. And triple to concatinate a string in while keeping the singles around it.

Ex.

DECLARE @some_string VARCHAR(500)

SET @some_string = 'Bulk Insert InLogDataSQL FROM ''' + @mypathstring + '''

WITH (

FIELDTERMINATOR = '' '',

FIRSTROW = 5,

ROWTERMINATOR = ''\N''

)'

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

conwaydean SSC Veteran Points: 279 More actions · Answer 2

It worked perfectly; and so ends my hours of toiling. Thanks a million for the help.

rachel SSCommitted Points: 1626 More actions · Answer 3

I have a different type of bulk insert question. I'm using a format file - and I have dbl checked its format with the table format that data is being inserted into. I keep getting a truncation error for the first source column. I don't understand why, when the source column is len 2, and the format file column specified len 2. Any help?

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Can you post the DDL of the table and the first few lines of data so I can see. Also what does the error state?

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

rachel SSCommitted Points: 1626 More actions · Answer 5

Sure - the table has one column varchar(17).

Lines of data

wb010120021.txt

wb010220021.txt

wb010320021.txt

wb010420021.txt

wb010520021.txt

for some reason, if I specify varchar(15) - which is the length of the data - I get a truncation error. I have to specify length 2 greater than the length of the data to avoid the truncation error. That is causing problems later b/c those 2 "extra" spaces, even though its varchar, can't be ltrim or rtrim out of the data.

Thanks for your help!

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Ok so you found a work around with issues. What do you mean though they cannot be rtrimed off. Can you not run

UPDATE tblX SET colV = RTRIM(colV)

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

rachel SSCommitted Points: 1626 More actions · Answer 7

Right - the rtrim does not get rid of them. This makes things difficult b/c that is a dynamically stored filename that I am trying to access - the 2 extra spaces spaces means "file not found".

Thanks for your help!

Antares686 SSC Guru Points: 125444 More actions · Answer 8

Then try

UPDATE tblX SET colV = LEFT(colV, LEN(colV) - 2)

since we know it is 2 spaces on the end. I will look into the other issue though.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Deuce SSCrazy Points: 2712 More actions · Answer 9

Sounds like there's a char(17) somewhere in the mix. Either that, or someone hexed your sql server.

A sure way to only get the first 15 characters is left(fieldname, 15). Make sure you're doing an rtrim(fieldname) when querying the filename from the table.

Good luck

John

Justin-14543 SSCrazy Points: 2017 More actions · Answer 10

Use Exec stmt

Exec(@some_string )

Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

rachel SSCommitted Points: 1626 More actions · Answer 11

Problem with quotes in the Bulk Insert - normally I use what you suggested

BULK INSERT temp FROM ''' (plus) @file (plus) '''

But in this case - my actual filename has quotes around it. It comes from a mainframe and for whatever reason thats the way the frame creates the filenames.

I tried using the "normal" way - but I get the following error:

Server: Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near 'ECM'.

(the filename is 'ECM.DM.LIST.0001000037.TEST' (quotes and all)

The actual syntax of the bulk insert (at run-time) is:

BULK INSERT temploadswbt FROM '\\Chicagofs\Ameritech\Sbc\'ECM.DM.LIST.0001000037.TEST''

WITH (

FORMATFILE = 'g:\sbcdata\swbtpb_new.fmt' )

anyone have any ideas? Thanks!!

Deuce SSCrazy Points: 2712 More actions · Answer 12

Deuce

SSCrazy

Points: 2712

September 26, 2002 at 2:47 pm

#437348

replace(filename, '"', '')

rachel SSCommitted Points: 1626 More actions · Answer 13

I am referencing an actual file that has quotes in the filename - if I take the quotes out of the variable holding the filename - it won't match the actual filename

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 14

Need another ' at the end try

Select '\\Chicagofs\Ameritech\Sbc\''ECM.DM.LIST.0001000037.TEST'''

and you get

\\Chicagofs\Ameritech\Sbc\'ECM.DM.LIST.0001000037.TEST'

which is what T-SQL will be using internally.