Menu
Is there an equivalent to Oracle's External Table in SQL Server ?
An external table is a table which is mapped to a flat-file in the filesystem.
It is very convenient since it allows you to read a flat-file as a table with standard SQL.
722
In SQL Server, the CREATE EXTERNAL TABLE statement creates the path and folder if it doesn't already exist. You can then use INSERT INTO to export data from a local SQL Server table to the external data source. For more information, see PolyBase Queries.
The external tables feature is a complement to existing SQL*Loader functionality. It enables you to access data in external sources as if it were in a table in the database.
An external table is a table whose data come from flat files stored outside of the database. Oracle can parse any file format supported by the SQL*Loader.
Overview: SQL Server 2016 Creates an external data source for PolyBase queries. External data sources are used to establish connectivity and support these primary use cases: Data virtualization and data load using PolyBase. Bulk load operations using BULK INSERT or OPENROWSET.
Harold Javier's answer is a good one,
but you might also want to consider using OPENROWSET with the BULK keyword.
It is different from the external table because you don't "create" a table but more of a query.
It should look something like this:
SELECT et.*
FROM OPENROWSET( BULK 'your_data_file', FORMATFILE = 'your_format_file.fmt') AS et
Instead of giving the format in the table definition (as in oracle), you should add a fmt file.
Here is how to create it
188
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
