CODEWITHSUNDEEP
sql-server
Ken
Ken

Reputation: 57

With SQL Server, I am trying to insert variable values into a Bulk Insert command with no success

DECLARE @filename VARCHAR(50)
DECLARE @dash VARCHAR(1)
DECLARE @month VARCHAR(3)
DECLARE @day VARCHAR(2)
DECLARE @year VARCHAR(4)
DECLARE @dlabel VARCHAR(10)
DECLARE @path VARCHAR(50)
DECLARE @fname VARCHAR(50)
DECLARE @Script VARCHAR(2048)

SELECT
    @filename = SUBSTRING(filenames, CHARINDEX('PSM', filenames) ,CHARINDEX('.csv', filenames) - CHARINDEX('PSM', filenames)) , 
    @month = CASE SUBSTRING(filenames, 47, 2)
                 WHEN '01' THEN 'jan' 
                 WHEN '02' THEN 'feb'
                 WHEN '03' THEN 'mar'
                 WHEN '04' THEN 'apr'
                 WHEN '05' THEN 'may'
                 WHEN '06' THEN 'jun'
                 WHEN '07' THEN 'jul'
                 WHEN '08' THEN 'aug'
                 WHEN '09' THEN 'sep'
                 WHEN '10' THEN 'oct'
                 WHEN '11' THEN 'nov'
                 WHEN '12' THEN 'dec'
                 END, 
    @day = SUBSTRING(filenames, 49, 2), 
    @year = SUBSTRING(filenames, 43, 4),
    @dlabel = CONCAT(CASE substring (filenames, 47, 2)
                         WHEN '01' THEN 'jan' 
                         WHEN '02' THEN 'feb'
                         WHEN '03' THEN 'mar'
                         WHEN '04' THEN 'apr'
                         WHEN '05' THEN 'may'
                         WHEN '06' THEN 'jun'
                         WHEN '07' THEN 'jul'
                         WHEN '08' THEN 'aug'
                         WHEN '09' THEN 'sep'
                         WHEN '10' THEN 'oct'
                         WHEN '11' THEN 'nov'
                         WHEN '12' THEN 'dec'
                     END, '_', SUBSTRING(filenames, 49, 2), '_', SUBSTRING(filenames, 43, 4))
FROM  
    dbo.files
WHERE 
    SUBSTRING(filenames, 40, 3) = 'PSM'
    AND filenames LIKE '%.csv%';


SET @fname = 'oars_results_final_' 
SET @filename = CONCAT(@fname, @month, @dash, @day, @dash, @year)
SET @dash = '_'
SET @path = '/Users/Public/' + @filename + '.csv'

SET @script= 'BULK INSERT ' +  @filename + ' FROM ' + @path + 
             ' WITH (FIRSTROW = 1, FIELDTERMINATOR = '','', ROWTERMINATOR=''\n'', BATCHSIZE = 250, MAXERRORS = 1)';

EXEC @Script;

Upon execution, I get the following error:

Msg 2812, Level 16, State 62, Line 145
Could not find stored procedure 'BULK INSERT oars_results_final_oct292021 FROM /Users/Public/oars_results_final_oct292021.csv WITH (FIRSTROW = 1,FIELDTERMINATOR = ',',ROWTERMINATOR='\n',BATCHSIZE=250,MAXERRORS=1)'

I am not using the @variable correctly I believe.

Upvotes: 0

Views: 115

Answers (1)

sticky bit
sticky bit

Reputation: 37472

To EXECUTE a code in a string, you need to put the string argument in parenthesis.

...
exec(@Script);
...

(Discalimer: I haven't checked if the code in the string is correct or makes any sense.)

Upvotes: 1

Related Questions