Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 416
  • Last Modified:

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

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
TPBPIT
Asked:
TPBPIT
  • 3
  • 2
1 Solution
 
vdr1620Commented:
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
 
TempDBACommented:
The ETL will be a better option. I second vdr1620 with using SSIS for this.
0
 
TPBPITAuthor Commented:
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
vdr1620Commented:
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
 
TPBPITAuthor Commented:
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
 
TPBPITAuthor Commented:
Thanks for pointing me to this info.  It took a while to figure it out, but I got it.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now