Improve company productivity with a Business Account.Sign Up

x
?
Solved

Loop Query - Database to CSV

Posted on 2011-02-21
5
Medium Priority
?
400 Views
Last Modified: 2013-11-10
Hello All,

im having a few problems,

What im trying to do is if i have two database tables with the same "field" names i would like to be able to take the data from the field names and insert them into a CSV file with just one set of headings for example if i had

Column 1 Column 2
Black       Sheep

In one table and...

Column 1 Column 2 (NOTE THAT THE COLUMNS ARE SAME NAME)
Pink          Cow

to make it do a loop for all tables within that database and insert all "Column 1 and column 2" data into one two headings in the CSV file..

If this is possible i really dont know which is why i turned to the guys that know best.

Thank you for your help it is always apprciated in any form.

James

p.s

So the ouput in the CSV i would like is as follows

Column 1 Column 2
Black        Sheep
Pink           Cow

Thanks again.
0
Comment
Question by:NeoAshura
  • 2
  • 2
5 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34945716
Try using BCP, create a temporary table and use union to populate the table then use BCP
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 34945777
I apologise for my ignorance.. But what is BCP and how do you create tempoary tables and unions?

Do you have any resources or tutorials i could look at? Thank you.
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 1200 total points
ID: 34945932
try like this
declare @sql varchar(8000)
create table ##mydata(Column1 varchar(50), Column2 varchar(50))
insert into ##mydata
select Column1 , Column2 from Table1
union all
select Column1 , Column2 from Table2

select @sql = 'bcp ##mydata out "c:\testtable1.csv" -c -t, -T -S'
exec master..xp_cmdshell @sql

drop table ##mydata

Open in new window

0
 
LVL 6

Author Comment

by:NeoAshura
ID: 34946009
Im so sorry, You are being really helpful but im so stupid where would that code go? would it go in mysql? or in my SSIS package somewhere?

Apologies again.
0
 
LVL 7

Assisted Solution

by:rmm2001
rmm2001 earned 800 total points
ID: 34989606
You can run that out of sql server management studio or put it as a sql task in a SSIS package.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Here is how to restore SQL Server database to the point in time.  Follow the step by step approach to restore your database at a specific point in time and also understand its alternate approach.
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.

588 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