SQL Server数据库中如何利用外部表-猿码集

1. 什么是外部表

在SQL Server数据库中，外部表是一种特殊的表，它不是存储在数据库中的，它是通过连接到外部数据源而创建出来的一种虚拟表。外部数据源可以是一个Excel文件、一个Access数据库、一个SQL Server数据库或者一个文本文件等等。外部表可以通过SQL语句对其进行访问和操作，与常规的内部表一样，可以进行Select、Insert、Update、Delete等操作。

2. 外部表的创建

2.1 使用SQL Server Management Studio(SSMS)创建外部表

在SSMS中，通过连接外部数据源，可以创建外部表。如下例子：创建一个连接到Excel文件的外部数据源tempDataSource。可以右击Server Objects，选择Linked Servers，然后在新的连接窗口中配置Excel的数据源，连接Excel文件即可。

EXEC master.dbo.sp_addlinkedserver @server = N'tempDataSource', @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\temp\test.xlsx', @provstr=N'Excel 12.0 Xml' GO

接着，在SSMS的Database上，右击New Query，选择‘New Query with Current Connection’，就会打开新的查询窗口。创建一个新的外部表tempTable，连接指向刚才创建的外部数据源tempDataSource。


CREATE EXTERNAL TABLE [dbo].[tempTable](
    [ID] [nvarchar](50) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Age] [int] NOT NULL,
    [Email] [nvarchar](50) NOT NULL
) WITH (
    DATA_SOURCE = tempDataSource,
    SCHEMA_NAME = N'dbo',
    OBJECT_NAME = N'test$'
)

在这个例子中，外部数据源连接到的是一个Excel文件test.xlsx，并在Excel文件中创建了一个Sheet1表。

2.2 使用T-SQL语句创建外部表

可以使用CREATE EXTERNAL DATA SOURCE指令和CREATE EXTERNAL TABLE指令来创建外部表。

CREATE EXTERNAL DATA SOURCE用于定义一个连接到外部数据源的数据源名称和连接信息。语法如下:


CREATE EXTERNAL DATA SOURCE data_source_name WITH ( 
    TYPE = type_name,
    LOCATION = 'location',
    CREDENTIAL = credential_name_or_external_provider
)

其中类型（type_name）取决于数据源类型，例如 SQL Server数据源应指定为 RDBMS。位置（location）取决于特定数据源的连接信息。凭据（credential）指定用于连接到外部数据源的凭据。例如，对于Azure Blob Storage数据源，它应该是一个有效的Azure存储账户凭据。

CREATE EXTERNAL TABLE用于创建外部表。语法如下:


CREATE EXTERNAL TABLE [ schema_name . ] table_name
(  [ ,...n ] )
WITH
( DATA_SOURCE = ,
  [  ]
)

其中，column_definition用于定义外部表的列和数据类型，data_source_name是外部数据源的名称，file_format_options表示数据源的格式，例如在使用Azure Blob Storage创建外部表的情况下，应该指定文件的文件类型、分隔符、封闭字符等信息。

3. 外部表的使用

3.1 查询外部表数据

可以使用SELECT语句对外部表进行查询。外部表查询需要与数据源交互，性能与数据源的规模、连接属性等有关。下面是一个例子，从一个存在于Azure Blob Storage上的csv文件的外部表系统中查询数据。


SELECT * FROM [dbo].[test_csv_external_tbl];

在这个例子中，外部表的名称是test_csv_external_tbl。如果您的数据源存在于Azure Blob Storage上，数据外部表是使用类似如下的SQL Server命令进行创建的：


CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
      LOCATION = 'https://mystorageaccount.blob.core.windows.net',
      CREDENTIAL= ****)
GO
CREATE EXTERNAL FILE FORMAT csv_format WITH (FORMAT_TYPE = DELIMITED,FORMAT_OPTIONS (FIELD_TERMINATOR = ',', STRING_DELIMITER='"', FIRST_ROW = 2,DATE_FORMAT = 'yyyy-MM-dd'))
GO
CREATE EXTERNAL TABLE [dbo].[test_csv_external_tbl]
(  
  [ID] [INT] NOT NULL,  
  [Name] [NVARCHAR](50) NOT NULL,  
  [Age] [INT] NOT NULL,  
  [Email] [NVARCHAR](50) NOT NULL  
)
WITH
(
  LOCATION='mycontainer/myfile.csv'  
, DATA_SOURCE=MyAzureBlobStorage  
, FILE_FORMAT = csv_format  
, REJECT_TYPE = value  
, REJECT_VALUE = 0  
);

3.2 更新外部表数据

在许多情况下，外部表充当一个只读表，更新需要在外部数据源中进行。但是，可以使用T-SQL对外部表进行更新，并将更新传递回数据源。例如：


UPDATE [dbo].[test_csv_external_tbl]
SET [Age] = [Age] + 1
WHERE [Name] = 'John'

在这个例子中，更新的查询发生在SQL Server实例中，但客户端处理的所有数据被传递回，以在一个独立的操作中更新同一数据源中的数据。

3.3 删除外部表数据

可以使用DELETE语句从外部表删除数据。在这种情况下，删除操作将传递回外部数据源。


DELETE [dbo].[test_csv_external_tbl]
WHERE [Name] = 'John'

3.4 插入外部表数据

可以使用INSERT语句将数据插入到外部表。数据将通过批量加载将插入到外部数据源中。例如：


INSERT INTO [dbo].[test_csv_external_tbl] ([ID], [Name], [Age], [Email])
VALUES (1, 'Mike', 25, 'mike@gmail.com'),
       (2, 'Tom', 28, 'tom@gmail.com')

在这里，将向外部表test_csv_external_tbl中添加两个记录。数据将在INSERT INTO语句执行时直接插入到连接到的外部数据源中。

4. 总结

外部表是SQL Server数据库中的一种特殊表，它通过连接到外部数据源来创建。可以通过T-SQL语句操作这个外部表以及它所连接的数据源。具体操作包括查询、更新、删除、插入等。外部表使用方便灵活，可以轻松连接到不同的数据源以简化复杂的数据处理工作。

SQL Server数据库中如何利用外部表