In an ideal world, we have the permissions we need to load data or work with data sets across servers, whether it’s through linked servers, dedicated staging tables,
In an ideal world, we have the permissions we need to load data or work with data sets across servers, whether it’s through linked servers, dedicated staging tables, OPENROWSET, BULK INSERT, or any number of other features designed to make ETL/ELT easy in SQL Server.
Not in my world.
This presentation explores ways to manipulate and load data from any arbitrary data source into one or more SQL Server tables or databases:
(1) without access to third-party apps (e.g. SSIS, Toad) or access to commonly-restricted functionality in SQL Server, and
(2) faster and with less locking than many commercial tools.
Examples will include loading data from .NET and VBA, but are easily adapted to anytime you have one or more sets of records that need to go from one place to another.
As a bonus, we’ll also talk about how to use linked servers / OPENQUERY *without* getting a nasty call from your DBA (especially when your reporting and operations databases are one in the same)