Solved

Exporting the a sql query to a csv file using the query

Posted on 2011-09-22
6
409 Views
Last Modified: 2012-05-12
The environment is sql 2008 R2.
I have attached a sql query that works fine to output the data I need.  The thing I would like to do is automate the script as much as possible as I will have to change c1alias and c2alias of the where statement out everytime I run the script.  Is there way to have the output saved as a csv file directly to a folder in the structure called c:\bbfolders\filename.csv.  The actual filename will be a combination of the values c1alias and c2alias that are used for the query.  The filename would be c1alias.c2alias.csv.  Can I even do what I want to do?  And if I can output it to a csv file is there a way to declare c1alias and c2 alias in the beginning so that I only have to enter these values onces each time I run the script?

I would be really nice if I could populate c1alias and c2alias from a text file or csv file and have each line run as a query and output the data as a seperate csv file, but that may be asking to much.  I have about 400 of these combinations that I need to run so anything that can make this faster is greatly appreciated.
SELECT
docnum, 
version, 
docname, 
author,
operator,
c1alias as 'client number',
c2alias as 'matter number',
c_alias as 'doc type',
t_alias as 'app type',
dateadd(HH,-6,editwhen) as 'Last Edit Date'

FROM dbo.docmaster

WHERE
c1alias='2314' and
c2alias='33861' 

ORDER BY docnum, version asc

Open in new window

0
Comment
Question by:TPBPIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 36582658
This would be easy to do with SSIS instead of using SQL query.. Let me know, if you are interested in using SSIS.. i will help you out
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 36582837
The ETL will be a better option. I second vdr1620 with using SSIS for this.
0
 

Author Comment

by:TPBPIT
ID: 36582913
I am familar with SSIS and yes, I would like to do this.  I know how to build a connection and output the data to a file.  How do I parse a text file to input the two fields into the sql script?
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 36583217
If i understand right.. the output file for the above query would be 2314.33861.csv ..  and you want all the result set of that query in that file.. Am i right ?

1. Use a execute SQL task to get the Combinations of the Where clause.
2. Store them in a variable or pass it as a parameter to the FEL (Ex: http://www.sanssql.com/2011/08/looping-through-sql-servers-using-ssis.html)
3. Use a Script task inside FEL to build the Connection string and store it in a variable and then substitute this variable for the CSV connection string using an expression

Or

you can use a Script task with few modifications to the example here (http://www.youdidwhatwithtsql.com/ssis-make-your-output-files-dynamic/616)

In a rush..so just briefed you on the process, try to follow the links and let me know if you have any Q's
0
 

Author Comment

by:TPBPIT
ID: 36583679
Not sure what to do with number 1.  How do I read a text file to get the to values for the where clause?  And how do I store all the combinations in a varible?  The next is how do you loop teh script for each combination?

Let me warn you that my query skills are somewhat limited.  It's pretty simple stuff.  I figured out SSIS, but still that is limited at best.
0
 

Author Closing Comment

by:TPBPIT
ID: 37106775
Thanks for pointing me to this info.  It took a while to figure it out, but I got it.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question