export Dynamiclly table to few tables

Question

Post reply

export Dynamiclly table to few tables

gilboa.reif

Grasshopper

Points: 19
More actions
June 3, 2014 at 12:50 am

#294072

Hi everyone,
I'm trying to export Huge tables (more than 100M rows) into few txt files according to value of one column
I have to lookup to another Huge table and exract some data from that too
I mean the table has a column named Institute_Code with values 1001,1002 etc
and the file should be called 1001.txt and contain all rows where Institute_Code = 1001
It's important that it will Work Dynamicly so that if ther's a new value in Institute_Code let's say 1009
the mechanism will be able to create this new file
each time the mechanism will run it overrides that prevous file
I tried SSIS loop container and it works fine
the trouble is that each iteration does the select statement and therfore it works really s-l-o-w
I thoght about command line Bulk Copy Program (BCP) but i'm not sure it suits because of the second table.
had anyone tried such a task?
I'll appreciate some assistance

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

gilboa.reif Grasshopper Points: 19 More actions · Answer 1

I'm sorry the title is wrong

should be export Dynamiclly table to few txt files

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

do you need the old data, that is if you have created a file 1009.txt before,

in the new file do u need the old data + New data or the only New data ?

gilboa.reif Grasshopper Points: 19 More actions · Answer 3

Each time i run it should write what's in the table and override the old file

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

It's not the export that's slow. It's the select. Please post it and the DDL (CREATE TABLE) for the two tables along with the CREATEs for the indexes and keys (can be done in a single stroke if your scripting options are setup correct.

Also, let's say that 1009 starts and then goes away on some date. Do you want the 1009 file to go away as well?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

the trouble is that each iteration does the select statement and therfore it works really s-l-o-w
I thoght about command line Bulk Copy Program (BCP) but i'm not sure it suits because of the second table.

You need to do the select statement in BCP as well, What you can do it to improve the performance of your select statement. like when you say 1009.txt for 1009 you need to join with other table to get the information, the joining column should be indexed if not already.

it would be great if DDL is shared along with the sample data which would be very helpful to illustrate the problem.