0

I'm trying to load data from a file (.txt) using T-SQL/SQL, I'm able to import that data but one of the requirements is that I need to pull the location of the file from a Table within the DB. Below is the Script I have. 

ALTER PROCEDURE [dbo].[sp_BULK_IMPORT]

@i_CUSTOMERID VARCHAR(10)
, @CFG_KEY VARCHAR(25)

AS              

DECLARE
    @DATE DATETIME
    , @WHO VARCHAR(20)
    , @CFG_VALUE VARCHAR(500)


SET @DATE = GETDATE()
SET @WHO = SYSTEM_USER


--Check for NULL CustomerID
IF ISNULL(@i_CUSTOMERID, '') = ''
    BEGIN
        SET @i_CUSTOMERID = '0'
    END

--Get File Directory for I9
SELECT @CFG_VALUE = sys_value FROM SYS_CONFIG(NOLOCK) WHERE sys_code = @CFG_KEY AND sys_customerid = @i_CUSTOMERID


/*GET DATE FROM FILE*/
BULK INSERT IMPORTED_DATA
FROM 'C:\File_Import\Import\I900000001.txt'
WITH
(
    ROWTERMINATOR = ';\n',
    CHECK_CONSTRAINTS
)

I want to replace "C:\File_Import\Import" with the variable @CFG_VALUE which is the value based on the Configuration in the system. When I do this I get an error:

EXPECTING ID, INTEGER, QUOTED_ID, STRING, or TEXT_LEX

Any help will be greatly appreciated.

jorame
  • 2,147
  • 12
  • 41
  • 58

1 Answers1

0

Hope you want like this

DECLARE @CreateSql varchar(max);
--or DECLARE @CreateSql varchar(x);

SET @CreateSql = 'BULK INSERT IMPORTED_DATA
FROM '+@CFG_VALUE+'  
WITH
(
    ROWTERMINATOR = ';',
    CHECK_CONSTRAINTS
)';

EXEC (@CreateSql);
aads
  • 2,255
  • 4
  • 24
  • 25