Sql-server – bulk insert for loas data

bulk-insertsql-server-2012

declare @DATADIR varchar(128)
set @DATADIR= 'C:\SQL2012\SH_DATA'

msg:Command(s) completed successfully.

EXECUTE('bulk insert CHANNELS from '''+@DATADIR+'\CHANNELS.txt''
        With (firstrow = 2,
 fieldterminator=''|'',
 KEEPNULLS,
rowterminator=''\n'',
 MAXERRORS = 10000)
 ')

msg:Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@DATADIR".

I am using SQL2012.

Best Answer

You shouldn't concatenate inside EXEC like that. Instead, try this (most importantly, it lets you debug your statement before executing it):

DECLARE @datadir VARCHAR(128) = 'C:\SQL2012\SH_DATA';

DECLARE @sql NVARCHAR(MAX) = N'BULK INSERT dbo.CHANNELS FROM ''' 
  + @DATADIR + '\CHANNELS.txt'' WITH ...;';

PRINT @sql;
-- EXEC sp_executesql @sql;

Also, you need to execute all three of these statements together. You can't execute the declares, then execute the remainder independently, which it seems like you did above.

Related Question