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.
We posted the same question in to Microsoft community and we found answer and the workaround from there.
Thanks everyone for the quick help.