How use dynamic Folder and File in Stored procedure?

SILS 61 Reputation points
2021-09-16T00:28:49.367+00:00

Sir

I have to import flat file into mssql 2019
But I have created stored procedure to import .txt file using asp.net page
I have fixed path and file name. Its working fine.
I need advice how to do ,user select folder and file name dynamically

It is my Code

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', 'Text;Database=C:\textfiles; ',
'SELECT CoCode, CoName, CoAddress1, CoAddress2, CoAddress, CoPost, SPeron, Transporter, DocDate, DocNo, 
Category, Amount, Type FROM test.txt')

Pls advice me
thank you
Maideen

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,363 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Lan Huang-MSFT 27,721 Reputation points Microsoft Vendor
    2021-09-16T03:30:19.147+00:00

    Hi @SILS ,
    Maybe you can build the statement as a string first, and then use EXECUTE to run it.
    You can refer to the following example:

    declare @query varchar(5000)  
    declare @FileName varchar(100)  
    declare @FilePath varchar(100)  
    set @FilePath = 'C:\textfiles';  
    set @FileName = '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'',''Text;Database=' + @FilePath + ''',  
    ''SELECT CoCode, CoName, CoAddress1, CoAddress2, CoAddress, CoPost, SPeron, Transporter, DocDate, DocNo,  
    Category, Amount, Type FROM ' + @FileName +''')';  
      
    print @query  
    execute(@query)  
    

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our  documentation  to enable e-mail notifications if you want to receive the related email notification for this thread.

    Best regards,
    Lan Huang

    0 comments No comments