1

Is it possible to use environment variables with the BULK INSERT command? I was really hoping this would 'just work':

BULK INSERT [dbo].[MyTable] FROM '%HOMEDRIVE%\Data\test.txt'
    WITH (FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n');

I realize this would be in respect to the server, not the client.

I'm sure I can get it to work with XP_cmdshell, but that seems excessive.

I'm actually looking to use another environment variable, I just picked %HOMEDRIVE% as a known example.

Robert Paulsen
  • 4,935
  • 3
  • 21
  • 27
  • Fas as I know you need either XP_cmdshell or CLR (if you thing XP_cmdshell is excessive, you'll probably think CLR is excessive as well) – HoneyBadger Aug 15 '16 at 16:15
  • SQL uses windows internal functionality to open the file, which does not recognize the variable. – Slava Murygin Aug 15 '16 at 17:37

1 Answers1

0

You cannot, but you can use XP_cmdshell or CLR as HoneyBadger suggested.

AS you will read elsewhere, enabling XP_cmdshell is not excessive but considered to be a security threat if taken lightly. It's because it give your SQL Server a whole lot more capability.

Now, my assumption is that you want to reuse the code for different variables. Assuming that you have the proper security context at the server level, one workaround would be to have a file that hosts the environment variables you want to maintain in a permanent location, and have some sort of scheduled scripts at the Windows level update it.

To use the content, you'll import it into your SQL Server via BULK INSERT or OPENDATASOURCE or whatever is within your permission to create dynamic SQL. Some example for XP_cmdshell can be found in the similar question here

Kentaro
  • 216
  • 1
  • 5