Data Integration

49.1 Import/Export Options

SSMS Import/Export Wizard, Import Flat File wizard, bcp, BULK INSERT, OPENROWSET(BULK...)

SqlPackage: DACPAC (schema) and BACPAC (schema + data) Generate Scripts (schema and/or data)

49.2 Linked Servers

Performance caveats: four-part queries may pull entire tables locally. Prefer OPENQUERY.

OPENROWSET / OPENDATASOURCE for ad hoc distributed queries.

EXEC sp_addlinkedserver @server = N'REMOTE1', @srvproduct = N'', @provider = N'MSOLEDBSQL', @datasrc = N'remote.corp.local';
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'REMOTE1', @useself = 'FALSE', @rmtuser = N'reader', @rmtpassword = N'***';

SELECT * FROM REMOTE1.SalesDB.dbo.Orders;               -- four-part name
SELECT * FROM OPENQUERY(REMOTE1, 'SELECT * FROM SalesDB.dbo.Orders WHERE OrderDate > ''2025-01-01''');  -- remote execution
EXEC ('SELECT @@VERSION') AT REMOTE1;

49.3 PolyBase and Data Virtualization

Query external data (Oracle, Teradata, MongoDB, other SQL Servers, ODBC, Parquet/CSV/Delta on S3 or Azure Storage) with T-SQL.

CREATE DATABASE SCOPED CREDENTIAL s3cred WITH IDENTITY = 'S3 Access Key', SECRET = '<key>:<secret>';
CREATE EXTERNAL DATA SOURCE s3ds WITH (LOCATION = 's3://s3.region.amazonaws.com/bucket', CREDENTIAL = s3cred);
SELECT * FROM OPENROWSET(BULK '/sales/*.parquet', FORMAT = 'PARQUET', DATA_SOURCE = 's3ds') AS s;

-- CETAS: Create External Table As Select (2022)
CREATE EXTERNAL TABLE dbo.SalesArchive WITH (LOCATION = '/archive/', DATA_SOURCE = s3ds, FILE_FORMAT = ParquetFF)
AS SELECT * FROM dbo.Orders WHERE OrderDate < '2023-01-01';

49.4 Calling REST Endpoints (Azure SQL / SQL Server 2025)

DECLARE @resp NVARCHAR(MAX);
EXEC sp_invoke_external_rest_endpoint @url = N'https://api.example.com/rates', @method = 'GET', @response = @resp OUTPUT;
SELECT JSON_VALUE(@resp, '$.result.rate');

49.5 Machine Learning Services

Run Python and R in-database with sp_execute_external_script. Java via language extensions.