Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I saw someone struggling with exporting some data from a table in a CSV, and decided to give it a try. I think there were a few things that were broken, but here is the basic idea.
The Scenario
I want to get all the data from a table into a CSV. As a setup, I have a table that looks like this:
What I’d like is this data in a CSV, with a format like this:
"CustomerKey","CustomerName","CustomerNameLen" "1","Steve","5" "2","Andy","4" "3","Brian","5" "5","Acme, Inc.","10" "6","Apple","5" "7","IBM","3" "1","Steve","5" "2","Andy","4" "3","Brian","5"
Let’s make that happen.
PoSh to the Rescue
If you have SQL Server and the Powershell module installed, you can use the Invoke-SqlCmd cmdlet. This takes parameters for a query and a server that you want to use. There are other parameters as well, but I’ll keep this simple.
The parameters I’ll use are a query with three part naming and then an instance. Here is the command:
Invoke-Sqlcmd -Query "SELECT * FROM [Sandbox].[dbo].[Customer]" -ServerInstance "Plato\SQL2017"
Now we want to get that data out to a CSV, and we can use the Export-Csv cmdlet for this. For me, I often want to avoid extra work, though in the past, I’d have set the output of the Invoke-SqlCmd to a variable. I don’t need to here, so I can use pipe the output to the cmdlet like this:
Invoke-Sqlcmd -Query "SELECT * FROM [Sandbox].[dbo].[Customer]" -ServerInstance "Plato\SQL2017" | Export-Csv -Path E:\Documents\sql\Customer.csv –NoTypeInformation
This will create a new file for me, called Customer.csv.
In here, we have our data.
Easy, and quick. You could even type this from the command line, without saving the code.
SQLNewBlogger
A quick look at a common task. In this case, I was helping someone, but I’ve had the need to do this myself, and fixing someone else’s code actually taught me something here. I’d use this as a story of solving a problem in an interview with new technology (PoSh).
Solving the problem was about 10 minutes work, and really, this took about 15 minutes to set up, get working, and clean the code a bit. I also had to change a few of the other issues to make this simpler, but those will be good posts in the future.
Voice of the DBA 