Solved

Loop Query - Database to CSV

Posted on 2011-02-21
5
383 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:ewangoya
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:
ewangoya 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
email the result out from a T-SQL queries 29 62
TSQL previous 5 23
SQL Server stored proc 2 12
What logic to build in order to get a weekly reminder 9 38
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now