Loop Query - Database to CSV

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.
LVL 6
NeoAshuraAsked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:
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
 
Ephraim WangoyaCommented:
Try using BCP, create a temporary table and use union to populate the table then use BCP
0
 
NeoAshuraAuthor Commented:
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
 
NeoAshuraAuthor Commented:
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
 
rmm2001Connect With a Mentor Commented:
You can run that out of sql server management studio or put it as a sql task in a SSIS package.
0
All Courses

From novice to tech pro — start learning today.