[SOLVED] External table Always insert in a new file, is there any way to write to same file?

External table Always insert in a new file, is there any way to write to same file?

I have a external table in SQL Server which points to CSV files in folder of Azure blob storage, I enabled polybase export and trying to insert data using insert query. It works but it always creates new file.

Is there any way I can write to single file or give file name while insert?

Here's my table

CREATE EXTERNAL TABLE archive.filetransferauditlog (
    [id] [int]  NULL,
    [STATUS] [varchar](10)  NULL,
    [EVENT] [varchar](10)  NULL,
    [fileNameWithPath] [varchar](2048) NULL,
    [eventStartDate] [datetime] NOT NULL,
    [eventEndDate] [datetime] NOT NULL,
    [description] [varchar](4096) NULL,
    [loggedInUserId] [int] NULL,
    [transferType] [int] NULL
    )
    WITH (
    LOCATION = '/filetransferauditlog/',
    DATA_SOURCE = archivepurgedataExternalDataSource,
    FILE_FORMAT = ParquetFile
    )
GO

Query I am using:

Insert into archive.filetransferauditlog
select Top(5)
from dbo.filetransferauditlog

Please suggest me any way we can give the file name while insert.

When I try to give location for table to a single file instead of directory, I am able to run select query but not insert.

It returns below error:

java.sql.SQLException: Cannot execute the query "Remote Query" against OLE DB provider "SQLNCLI11" for linked server "SQLNCLI11". CREATE EXTERNAL TABLE AS SELECT statement failed as the path name 'wasbs://demoarchive@testarchivedemo.blob.core.windows.net/filetransferauditlogText/QID5060_20220607_54101_0.txt' could not be used for export. Please ensure that the specified path is a directory which exists or can be created, and that files can be created in that directory.

Solution