Load data into 7 different tables named with the prefix

Question

Load data into 7 different tables named with the prefix

peterk1961

SSC-Addicted

Points: 493
More actions
March 16, 2009 at 12:13 pm

#129094

Hi,
We are in the process of upgrading sql 2000 to sql 2005 and I am looking at the different DTS packages to see how I can get it to work in sql 2005.
One of the process is to import data from mainframe and load it into an intermediate table. The data finally gets loaded into 7 different tables depending on the date of transaction. We retain data for 7 months so each table represents one month's data.
In sql 2000, the final (production) tables are named xxx_01, 02, etc. The import table has a column named prodprefix which has the value '01', '02' etc. If that value is '01', then that row gets inserted into xxx_01 table. This is done by 7 different data transformations in 2000.
Sql 2005 has the ability to loop through so I was wondering if it is possible to keep incrementing the value from 01 to 07 and use that variable field with in a sql statement as well as in the OLE DB destination table name.
If I didn't explain the process or didn't make myself clear, please feel free to ask.
Thanks.
Peter Kennedy

Viewing 5 posts - 1 through 4 (of 4 total)

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

Old McDonald SSCertifiable Points: 5768 More actions · Answer 1

There is no need to loop on the records

You can make use of "Conditional Split" Data Transformation to achieve this. Put the condition on the value of the source table column containing 01, 02...07 and redirect the data in the appropriate table.

Only 1 Conditional Split will be required to do this.

-Vikas Bindra

peterk1961 SSC-Addicted Points: 493 More actions · Answer 2

Thanks for the quick response. I will give that a try.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

After the data is in the intermediate table, can you just fire off seven INSERT commands?

INSERT INTO table1(,,,,,)

SELECT .... from intTable where ProdPrefix = '01'

INSERT INTO table2(,,,,,)

etc etc

Should be faster. I assume you've got an index on ProdPrefix already ...

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

peterk1961 SSC-Addicted Points: 493 More actions · Answer 4

That is kind of the easiest route but I just wanted to know if sql 2005 had any other way that can accomplish the same in a more efficient way.

Thanks.

Peter Kennedy