Thursday, November 01, 2007

Importing Text-based data: Workbench

Alternatives to SSIS & DTS are built-in to T-SQL 

It is hard to estimate the enormous number of unnecessry and unmaintainable SSIS and DTS files that are written merely to import data from text into SQL Server. For performance, and for the sanity of the DBA, it is usually better to allow SQL Server to import text and to pummel it into normalised relational tables, rather than rely on procedural techniques.

There are many ways to read text into SQL Server including, amongst others, BCP, BULK INSERT, OPENROWSET, OPENDATASOURCE, OPENQUERY, or by setting up a linked server.

Normally, for reading in a table from an external source such as a text file, one would use an OpenRowSet, which can be referenced in the FROM clause of a query as though it were a table name. This is a topic that would take too long to tackle in this workbench, though we'll show you an example of its use for reading in a CSV file. Perhaps one day we'll do an OpenRowSet Workbench!...

Importing Text-based data: Workbench

No comments: