How to use relative path in BULK INSERT

Question

Post reply

How to use relative path in BULK INSERT

George Lu-279482

SSC Veteran

Points: 293
More actions
May 23, 2006 at 12:55 pm

#112414

Bulk Insert takes full path for data file and format file. This is sometimes not desired, as now I store data in %TMP% and format file in relative path.
I guess we cannot change the way BULK INSERT doing things. But is there a way in SQL environment to get full name of the current path and the system temp path %TMP%.
Thanks

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

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Try calling API

ExpandEnvironmentVariable

N 56°04'39.16"
E 12°55'05.25"

George Lu-279482 SSC Veteran Points: 293 More actions · Answer 2

Thanks.

Is this API in SQL environment or .NET environment ? Can it be called in SQL 2000 Trans-SQL ? I could not find it in BOL.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Try this from Query Analyzer...

EXEC Master.dbo.xp_DirTree 'C:\',0,1

Then, read up on the dangers of using undocumented features. Also keep in mind that Microsoft can also change or eliminate API's just as quickly as they can undocumented features.

By the way, the "0" in the above controls how many levels to search down. "0" means all levels... any other number is the number of levels to search.

The "1" means to list files names, as well. Try it and put the output into a table (you MUST have an autonumbering clustered PK on the table for the output to be usable in multi-levels) using INSERT/EXEC.

Unlike xp_CmdShell, everyone can use xp_DirTree...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vadba SSChampion Points: 11133 More actions · Answer 4

You can get the value of the TMP environment variable (on the server) using something like this:

CREATE TABLE #path

(

tmpPath varchar(200)

)

DECLARE @path varchar(200)

INSERT #path EXEC master.dbo.xp_cmdshell 'SET TMP'

SELECT @path = tmpPath FROM #path WHERE tmpPath IS NOT NULL

PRINT @path

DROP TABLE #path