Need to insert column with Table name in table

Question

Need to insert column with Table name in table

nabaj

SSC-Addicted

Points: 410
More actions
March 27, 2011 at 11:50 pm

#237368

Hi,
I am working on a project where we are taking expense data from different dept tables and making one big table that will contain all the expense information. We also need to add a column in the new table which will have the last three digits of the table it came from.
Ex: SSE_COR (columnA, columnB, columnC) is one of the tables.
So, the new table will be SSE_EXPENSES (Column1, ColumnA, ColumnC), I will only take the required data columns.
The column1 will have the value 'COR' for every record inserted in SSE_EXPENSES from SSE_COR.
Thanks in advance for any help

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

I'm not sure what the question is. Create the new table and when you do inserts into it from the other tables, hardcode the 3 letters from each table. I think this is just a simple INSERT/SELECT and SSIS isn't actually needed.

--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)

nabaj SSC-Addicted Points: 410 More actions · Answer 2

I am sorry but maybe i was not clear enough in my question..

I have about 72 dept tables and each table has 50,000 + records.

I need to create a package that will pick up the records from each table along with the last three digits of the table and insert them into the EXPENSES table. So, the expenses table will have all the columns from the dept table along with an extra column named dept which will contain the last three digits of the dept table.

Hope this is a little better.

Thanks,

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

As Jeff said, if your consolidated EXPENSES table will reside in the same instance as the 72 dept tables then you do not need SSIS.

If you are looking into SSIS because you need to send this data to an external destination then I have a couple more questions:

1. Are you looking to do this dynamically? i.e. will a 73rd, 74th, 75th, etc. table be added later and be named per the existing naming convention and so you're looking for the SSIS package to handle this scenario automatically with no code changes? A dynamic approach will also handle the case when a dept table is dropped. If this is the case, it can be done, however it will require you to generate SQL dynamically, either in T-SQL or using SSIS expressions.

2. If the set of dept tables will remain relatively static and you would not mind some simple, manual, code changes each time the set of tables changes then you'll have a one-time task to create a SELECT statement that generates the resultset you need. I would recommend storing the SELECT as a VIEW in SQL Server and selecting from the VIEW from SSIS.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato