Solved

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

Posted on 2011-09-22
6
402 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now