Altering EXTERNAL TABLES

Question

All,

I need to create external tables against data sources that are not defined in advance.

If the External Table is of the form:

CREATE EXTERNAL TABLE dbo.InventoryInfo_EXT
(
Col1 int,
Col2 varchar(100))
with (DATA_SOURCE = [InventorySrc], LOCATION = 'InventDB.dbo.InventoryInfo')

I would want to be able to change the Table Name, DATA_SOURCE and LOCATION dynamically in a procedure.

I'm aware that ALTER did not exist external tables and I want to check if this is still the case.

In the absence of it, how can this be best handled - using execsql?

Thanks,
grajee

Answer 1

Hi @grajee ，

If you would like to change the Table Name, DATA_SOURCE and LOCATION dynamically in a procedure, you also need to change the column defination since different tables have different columns.

You could refer below code and check whether it is helpful to you.

SQL Server 2016 and earlier:

declare @sql nvarchar(max)  
declare @tablename varchar(100)='dbo.InventoryInfo_EXT'  
declare @DATASOURCE varchar(100)='InventorySrc'  
declare @LOCATION  varchar(100)='InventDB.dbo.InventoryInfo'  
declare @columns nvarchar(max)  
  
;with cte as (  
SELECT COLUMN_NAME,  
  CASE  
    WHEN ISNULL(NUMERIC_PRECISION, 200) <> 200 THEN DATA_TYPE + '('  
         + CAST(NUMERIC_PRECISION AS VARCHAR(5))  
         + ',' + CAST(NUMERIC_SCALE AS VARCHAR(5))  
         + ')'  
    WHEN ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) = 0 THEN DATA_TYPE  
  ELSE  
    DATA_TYPE + '('  
      + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))  
      + ')'  
  END  DATA_TYPE   
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = PARSENAME(@LOCATION,1))  
select @columns=STUFF((  
            SELECT ',' + COLUMN_NAME+ ' '+ DATA_TYPE+' '  
            FROM cte  
            FOR XML PATH('')  
            ), 1, 1, '')  
 from cte   
  
set @columns= SUBSTRING(@columns,2,len(@columns)-1)  
  
set @sql= N' CREATE EXTERNAL TABLE '+@tablename+' (  
'+@columns+'  
)  
with (DATA_SOURCE ='+@DATASOURCE+', LOCATION = '''+@LOCATION+''')'  
  
EXECUTE sp_executesql  @sql  

SQL Server 2017 and after:

declare @sql nvarchar(max)  
declare @tablename varchar(100)='dbo.InventoryInfo_EXT'  
declare @DATASOURCE varchar(100)='InventorySrc'  
declare @LOCATION  varchar(100)='InventDB.dbo.InventoryInfo'  
declare @columns nvarchar(max)  
  
;with cte as (  
SELECT COLUMN_NAME,  
  CASE  
    WHEN ISNULL(NUMERIC_PRECISION, 200) <> 200 THEN DATA_TYPE + '('  
         + CAST(NUMERIC_PRECISION AS VARCHAR(5))  
         + ',' + CAST(NUMERIC_SCALE AS VARCHAR(5))  
         + ')'  
    WHEN ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) = 0 THEN DATA_TYPE  
  ELSE  
    DATA_TYPE + '('  
      + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))  
      + ')'  
  END  DATA_TYPE   
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = PARSENAME(@LOCATION,1))  
select @columns=STRING_AGG(CONVERT(NVARCHAR(max), CONCAT(',',COLUMN_NAME, ' ', DATA_TYPE, ' ')), CHAR(13))   
from cte  
  
set @columns= SUBSTRING(@columns,2,len(@columns)-1)  
  
set @sql= N' CREATE EXTERNAL TABLE '+@tablename+' (  
'+@columns+'  
)  
with (DATA_SOURCE ='+@DATASOURCE+', LOCATION = '''+@LOCATION+''')'  
  
EXECUTE sp_executesql  @sql  

In addition, about Altering EXTERNAL TABLES, you could refer more details from below links and check whether any of them is helpful.
Create and alter external SQL tables
ALTER EXTERNAL TABLE

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet