?
Solved

Loop Query - Database to CSV

Posted on 2011-02-21
5
Medium Priority
?
396 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
[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
  • 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

770 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