Ever found yourself swimming in an ocean of data, wondering the best way to import it into your SQL Server? You’re not alone. SQL Server 2022 comes to the rescue with a trio of tools designed to streamline this process: BCP, BULK INSERT, and OPENROWSET. Each has its unique flair for handling data, and I’m here to guide you through choosing the right tool for the job with some handy T-SQL snippets.

Making The Choice

Choosing between BCP, BULK INSERT, and OPENROWSET boils down to your needs:

• BCP shines for its speed and simplicity, perfect for external data moves.
• BULK INSERT keeps you in control within SQL scripts, ideal for routine loads.
• OPENROWSET offers unparalleled flexibility for direct queries, great for those special occasions.

BCP (Bulk Copy Program)

Picture BCP as your go-to for moving massive data sets. It’s the old reliable, working outside the SQL environment to push or pull data with the speed of light. Well, almost.

Here’s How You Do It:

Exporting data to a text file feels like a breeze:

bcp YourDatabase.dbo.YourTable out C:\data\YourTable.txt -c -T -S YourServer

And bringing it back? Just as easy:

bcp YourDatabase.dbo.YourTable in C:\data\YourTable.txt -c -T -S YourServer

With -c for character data, -T for trusty integrated security, and -S to specify your server, you’re all set.

BULK INSERT

Now, if you’re the type who loves to keep things in script, BULK INSERT is your best friend. It lets you snugly import data files right from a T-SQL script, offering control and convenience within your SQL habitat.

Let’s Get To It:

BULK INSERT YourDatabase.dbo.YourTable
FROM 'C:\data\YourTable.txt'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);

This magic line whispers to SQL Server to draw data from YourTable.txt into YourTable, using commas to separate the fields and newline characters to mark the end of each row.

OPENROWSET

Ever wished you could just query data straight from a file as if it were another table? OPENROWSET grants that wish. It’s the wildcard that allows you to integrate data files into your queries, perfect for those one-off or ad-hoc tasks.

Here’s The Spell:

INSERT INTO YourDatabase.dbo.YourTable (Column1, Column2, Column3)
SELECT *
FROM OPENROWSET(BULK 'C:\data\YourTable.txt', SINGLE_BLOB) AS DataFile

This incantation pulls data directly into your table, treating the file as a blob for maximum flexibility.

Armed with these tools, you’re ready to tackle any data import challenge that comes your way in SQL Server 2022. Dive in, experiment, and discover the best fit for your data adventures!