You can first build the statement into a string and then use
EXECUTE (Transact-SQL) - SQL Server | Microsoft Docs[
^] to run it.
Consider the following example
declare @location as varchar(100)
declare @tablename as varchar(100)
declare @query varchar(5000)
set @location = 'C:\textfiles';
set @tablename = 'test.txt';
set @query = 'insert into [dbo].[ZZ_Flat_Data]
(CoCode, CoName, CoAddress1, CoAddress2, CoAddress, CoPost, SPeron, Transporter, DocDate, DocNo,
Category, Amount, Type)
SELECT * FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''' + @location + ''',
''SELECT * FROM ' + @tablename +''')';
print @query
execute(@query)
This would build the string and print it as follows
insert into [dbo].[ZZ_Flat_Data]
(CoCode, CoName, CoAddress1, CoAddress2, CoAddress, CoPost, SPeron, Transporter, DocDate, DocNo,
Category, Amount, Type)
SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'C:\textfiles',
'SELECT * FROM test.txt')
and the run it using execute