August 6, 2008 at 9:20 am
Hi, sorry if this in the wrong forum topic, but I found it most apropos to my question.
Im running MSFT SQL Server Mgmt Studio 2005 and would like to export all tables where # of rows > 0. I would like to export all rows to XLS files. I've gone through BCP documentation to no avail. Is there a simple code that can just export all tables in a database (with # rows > 0) to individual XLS files?
Thank you in advance for all helpful responses!
August 6, 2008 at 9:47 am
It probably wouldn't be that difficult to build something to do that in SSIS.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2008 at 10:24 am
Perhaps not, but Im not as strong in SSIS. I'm really just looking for a simple script I can run to output all tables with rows > 0 to individual XLS files.
August 6, 2008 at 11:21 am
Well, you'd have to select a list of tables, and their rows, then create a dynamic export script to create the files and export to them.
I don't think it's a trivial "simple script" thing. I could be wrong, but I think it's gonna take some time and work to build it yourself.
SSIS, on the other hand, will do most of the work for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2008 at 12:47 pm
Well, the tables I want to output to XLS are created dynamically by a script. So, I wont know the table names until after running said script. I dont think theres any way in SSIS to create something which goes out to the DB, selects all tables with record_count>0, and then outputs to XLS. I think you need to know the specific table names, am I wrong?
August 7, 2008 at 11:00 am
SSIS can select from the system views, then do a For Each Next loop over the tables.
So, of course, can dynamic SQL.
Go with whatever you're comfortable with.
I have to say, it seems quite strange to me that you'd have a database with constantly changing tables, and then want to export them to dynamically generated xls files. It might actually be easier to manage such a system in VB/C# than it is in T-SQL/SSIS. But it does seem like an odd use of a database. What's it for, if you don't mind saying?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply