Solved

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

Posted on 2011-09-22
6
406 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

773 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