I have SQL Server 2019 in Docker Container with polybase enabled and I am connecting to SQL Server 2019 On Premise with polybase enabled. I created external table in Docker -SQL Server.
I also allowed polybase export, restarted the on-premise sql server 2019.
EXEC sp_configure 'allow polybase export', 1;
RECONFIGURE
SELECT works fine. but when I try to Insert
INSERT INTO Student
select 3, 'Amaya', 'A+'
Error:
Started executing query at Line 53
Msg 46519, Level 16, State 16, Line
DML Operations are not supported with external tables.
Total execution time: 00:00:00.107
External tables are read only. As the error tells you, you can't perform DML write-operations (such as INSERT, UPDATE, etc) on them.
This is also documented in the article CREATE EXTERNAL TABLE (Transact-SQL): Limitations and Restrictions:
Only these Data Definition Language (DDL) statements are allowed on external
tables:
- CREATE TABLE and DROP TABLE
- CREATE STATISTICS and DROP STATISTICS
- CREATE VIEW and DROP VIEW
Constructs and operations not supported:
- The DEFAULT constraint on external table columns
- Data Manipulation Language (DML) operations of delete, insert, and update
Emphasis mine.