Solved

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

Posted on 2011-09-22
6
412 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

622 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