Split Table to 2 tables in DTS

JMarais
JMarais used Ask the Experts™
on
I have one source table with field1, field2, field3 and field4.  I need to put field1 and field2 in DestinationTable1,  return (with @@Identity?) the DestinationTable1ID (which is a numeric identity column),  and then put that value in DestinationTable2 (foreign key pointing to DestinationTable1), along with field3 and field4.

I am a beginner, I have tried everything I could think of.  I could not execute stored procedures from within a Data Driven Query Task, maybe I'm on the wrong track completely.  Im using an SQL server 2000 database.  It has to be a DTS package.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I think this has to be a two step process built into DTS.

one step to loaf field1 and field 2 into table1

another to read field 3, field4 and identity column from table 1 and load into table 2.

With this you will be reading the source table twice, but I can't think of any other thing right now.

How big is your source table , how many rows are you reading ?

Author

Commented:
The problem is that when you do the second step, you don't know what the system generated Primary key is of DestinationTable1.  In the mean time, I have found that DTS will not insert a record if the table has a identity column.  I do not want to set Identity_insert on, as I need the system to give me the field.  So this complicates it further.
I wrote a cursor in a Stored Procedure to do this, but it is obviously a slow method.  I was hoping DTS has a faster way.
I have not been given data yet, so I don't know how big.
It looks like I will have to go for using the cursor.
For the statement you made "The problem is that when you do the second step, you don't know what the system generated Primary key is of DestinationTable1"

I am not sure I understand the problem correctly, I am thinking you would know how you can join source table and your DestinationTable1 to get the field3, field 4 and the already created identity column from DestinationTable1. Dont u have a way of joining your source and DestinationTable1.

First step I think would be to load the source table into a work table in destination and use it as source for loading your two destination tables.

" In the mean time, I have found that DTS will not insert a record if the table has a identity column.  I do not want to set Identity_insert on, as I need the system to give me the field.  So this complicates it further"
Here are you talking about inserting into DestinationTable1 or DestinationTable2?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I'm talking about DestinationTable1.  DTS will not insert a record if the table has a identity column.  That is the problem.  So now I am just running a cursor.  It works ok, but is a bit slow.

Author

Commented:
Looks like there is no solution.  The workaround I am using is working fine.
PAQed with points refunded (500)

Computer101
EE Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial