Solved

Loop Query - Database to CSV

Posted on 2011-02-21
5
390 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 300 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 200 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

740 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