Using bcp for Simple Exports

Using lures for fishing is often quite an effective method, but there is rarely anything that can beat live bait.  Having bait that can sit right in front of the fish’s mouth, as well as be smelled and tasted as an additional temptation, is a great way to catch fish.  I usually prefer the action of casting and retrieving to slowly dragging bait around the lake.  However, sometimes it can be great to sit back and sip a beer while catching fish.

Sometimes it can be nice to do things the easy way.  I learned how to import and export data from SQL Server using SSIS.  However, this is a full blown ETL tool and is overkill for some tasks that can be very simple, such as exporting an entire table into a CSV file.  Recently I had to move some data from our on premise SQL Server into an Azure SQL Data Warehouse.  The tutorial that I was following suggested first moving the data into Azure blob storage, then importing it into the Data Warehouse.  I used bcp to export the data from my on premise database into the Azure blob storage and couldn’t believe how easy it was.  I will certainly be using bcp for simple imports and exports when possible in the future.  Here is an example of using bcp:


CREATE TABLE dbo.LiveBait (id_livebait INT IDENTITY(1,1), baitname VARCHAR(25), defaultqty INT)
INSERT INTO dbo.LiveBait
        (  baitname, defaultqty )
VALUES  ( 'Wax Worms',  36   ),
       ( 'Nightcrawlers',  12   ),
       ( 'Red Worms',  24   ),
       ( 'Crappie Minnows',  12   ),
       ( 'Pike Suckers',  6   ),
       ( 'Muskie Suckers',  1   ),
       ( 'Shiners',  12   ),
       ( 'Frogs',  2   ),
       ( 'Salamander',  1   ),
       ( 'Leeches',  18   )

20160307 bcp

Here are some of the key bcp arguments from MSDN.

20160307 bcp arg1

20160307 bcp arg2

20160307 bcp arg3