I Want To Use The Variable I Declared Somewhere Else But I Cannot (simple Sql Query)

DECLARE @path text; SET @path = 'c:\bulk' BULK INSERT [HumanResources].[Employee] FROM -- I Want to use the variable here !! WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP',

Solution 1:

DECLARE @path nvarchar(2000);

    SET @path = 'c:\bulk.(extension)';
    DECLARE @sql NVARCHAR(MAX) =

    '''BULK INSERT [HumanResources].[Employee] FROM' + @path  + '
    WITH (
        CHECK_CONSTRAINTS,
        CODEPAGE=''ACP'',
        DATAFILETYPE=''widechar'',
        FIELDTERMINATOR=''\t'',
        ROWTERMINATOR=''\n'',
        KEEPIDENTITY,
        TABLOCK
    )'''

    EXECUTE sp_executesql(@sql) 

Solution 2:

How about this query? Uses dynamic query to execute query. Just aware of the single quotes.

 DECLARE @path nvarchar(2000);
DECLARE @sql nvarchar(2000);
SET @path = 'c:\bulk.txt'

set @sql = 'BULK INSERT [HumanResources].[Employee] FROM ''' + @path + ''' WITH (CHECK_CONSTRAINTS,  CODEPAGE=''ACP'',
    DATAFILETYPE=''widechar'',
    FIELDTERMINATOR=''\t'',
    ROWTERMINATOR=''\n'',
    KEEPIDENTITY,
    TABLOCK
)'

print @sql
exec sp_executesql @sql