How to write multiple files in SQL, from a query result?

Question

I have a data set of a 1m+ records. I need to output the query to multiple files depending on a order by .

Example Data set:

ID        StoreName      StoreChain   MoreData........
 1        walmart NY     Walmart      ...
 2        Walmarty NJ    Walmart      ....
 3        Target NY      Target       ....
 4        Costco NY      Costco       ....

Example Query

 Select StoreName, SotreChain,moreData order by StoreName

I need to write for each store name. (4 files)

Select StoreName, StoreChain,moreData order by StoreChain

I need to write a file for each Chain, (3 files)

Today I use a CLR SP, it works fine but is a little slow (1h for 1000 files of 200-400 lines each)

I want to know if there is a pure T-SQL solution, if needed it can use the command shell.

and it has to write to a log table every time it creates a file.

user596075 · Accepted Answer · 2011-12-29 13:39:58Z

3

I believe the best solution here would be to use a SQL Server Integration Services (SSIS) package. You can design this using Business Intelligence Development Studio (BIDS).

Using this, you can specify your T-SQL as the source and flat files as the destination of your queries.

You can even go as far to schedule the SSIS package to run routinely.

answered Dec 29, 2011 at 13:39

user596075

Sign up to request clarification or add additional context in comments.

1 Comment

vaquito Over a year ago

This can work, but the tables are created, consumed and destroy and there is a second table that determines witch columns dose the file contains. I know did´t specify this in the question so I´ll accept your response by the end of the day.

ilija veselica · Accepted Answer · 2011-12-29 13:52:30Z

1

I agree with Shark. You should try doing this using SSIS packages. Here are links to a couple of tutorials:

When you figure out how to use the tool you will be able to set up ssis package to import data from huge table to another in less than a minute. I definitely recommend this option if you plan to deal with large amount of data

answered Dec 29, 2011 at 13:52

ilija veselica

9,59239 gold badges97 silver badges153 bronze badges

Collectives™ on Stack Overflow

How to write multiple files in SQL, from a query result?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related