asked on
Split Table to 2 tables in DTS
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.
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.
Microsoft SQL Server
Last Comment
Computer101
ASKER
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.
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?
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?
ASKER
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.
ASKER
Looks like there is no solution. The workaround I am using is working fine.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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 ?