Execute a TSQL command using variables

Question

I have files that I need to bulk insert into SQL server:

@Date char(8) = '20200429' *--this is a specific file variable*

so the paths are as follows:

root path: @ROOT = 'G:\app\Data\table-dump\'
filepath: @FILEPATH = @date+'\'+@Date+'-230000-file.txt'
fullpath: @FULLPATH = @ROOT + @FILEPATH

I want to run a bulk insert into a table called dbo.TableDump_IMPORT

SO I constructed this:

DECLARE @BULKINSERT as varchar(max) 
SET @BULKINSERT= 'BULK INSERT dbo.TableDump_IMPORT FROM ' + @FULLPATH + ' WITH (    FIRSTROW = 2,   FIELDTERMINATOR = ''|'',    ROWTERMINATOR = ''0x0A'')'
EXEC @BULKINSERT

When I run the command I get this error:

The name 'BULK INSERT dbo.TableDump_IMPORT FROM G:\app\Data\table-dump\20200429\20200429-230000-file.txt WITH ( FIRSTROW = 2,   FIELDTERMINATOR = '|',  ROWTERMINATOR = '0x0A')' is not a valid identifier.

How do I get it to just execute the BULK INSERT command?

EXEC (@BULKINSERT)

Zhorov
– Zhorov

2020-05-07 12:27:21 +00:00
Commented May 7, 2020 at 12:27 — Zhorov
– Zhorov, Commented May 7, 2020 at 12:27

pim · Accepted Answer · 2020-05-07 12:28:38Z

1

You need to enclose the filepath in single quotes.

SET @BULKINSERT= 'BULK INSERT dbo.TableDump_IMPORT FROM ''' + @FULLPATH + ''' WITH ( FIRSTROW = 2, FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''0x0A'')'

answered May 7, 2020 at 12:28

pim

12.7k6 gold badges71 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dragonfury2 Over a year ago

A combination of this and EXEC (@BULKINSERT) worked

Collectives™ on Stack Overflow

Execute a TSQL command using variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related