Search code examples
sql-servert-sqlsql-server-2016polybasesql-server-2019

Import external table

I created an external table using polybase with 

CREATE EXTERNAL TABLE [ext].[gendertable]
( 
        gender_id TINYINT NOT NULL,
        gender VARCHAR(16) NOT NULL
) 
WITH 
( 
    LOCATION = '/MovieDB/gender.csv', 
    DATA_SOURCE = AzureBlobHDP, 
    FILE_FORMAT = csvformat0
);
GO

The data source is HADOOP. Is there a way to import this table without defining data type again for every column? I search for some code like this:

CREATE TABLE [mov].[gendertable]
WITH
( 
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT * FROM [ext].[gendertable]

Of course this code fails for me, since I do not use an Azure SQL DW (I get a Syntax error). I use SQL Server 2019 on a VM.

My question is there is a SQL expression, such that I do not have to declare the data type for each column?

Solution

  • Actually it is quite simple:

    SELECT * 
INTO [mov].gendertable
FROM [ext].[gendertable];
GO