Forum Discussion
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://email address removed for privacy reasons/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.
- bake13Microsoft
Hi jigarjadav89 -- This is not possible with PolyBase. PolyBase uses WASB for Azure Blob Storage which is much like HDFS in that it is an append-only filesystem. That is one of the advantages or use cases for external tables in that they enable interaction the data using standard TSQL syntax while the abstracting the physical storage of the data. Take care.
Hi jigarjadav89
You can use Azure Logic app to create or append the information from the new files once they created into a new single file.
For the task you simply use Azure Logic Apps trigger named "When blob is added or modified".
Once a new file will be added then this app will run and in the app you can append the data from the new file to the existing file.
Since this action will be triggers by external app and not related to the database server, it will NOT reduce any performance of your server, which make this solution useful
- jigarjadav89Copper ContributorRonen_Ariely, Thanks for the response, It was one of our plan to do append through other app, we planned using ssis, but this looks a good solution, we will try this.
- You are most welcome 🙂