DTS question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS question

Author

Topic

super_julius
Starting Member

2 Posts

Posted - 2004-04-29 : 09:04:41

Hi Folks,

I am looking at DTS to evaluate whether we could use it to "integrate" two systems that are both using SQL databases. Basically we have a new system that will need to write data to an old system before it is replaced.

The new system has 3 tables A, B, C. Tables B and C are linked to A using the table A PK.
The old system has 5 tables but we can assume that it also has 3 tables A1, B1, C1 to simplify the problem. The same way, tables B1 and C1 are linked to A1 using the table A1 PK. In both case the PK column is an int auto incremented.

So far I managed to transform data from A1 to A. The transformation recreates the A1 PK.

My problem is how do I retrieve the newly generated A1 PK to transform B1 and C1 to respectively B and C, and linked B and C to A.

Hope this is clear enough,

Thanks in advance for your input

Julius

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-04-29 : 09:37:42

Hope this should give you a start...
1. Create a new column for Identity Creation on Table A1.
2. Insert A with that new id.
3. Join A, B with old pk values and insert with the new indentity.

Alter Table A1 add column newIdent numeric
GO
Begin Tran
Declare @nid numeric
Select @nid = max(col1) from A

update A1 set @nid = newIdent = @nid + 1

Set identity_insert ON A

Insert into A Select newIdent, col2 from A1

Insert into B Select A1.newIdent, B.col1, B.col2
from A1 Inner join B1 on A1.pkcol = B1.lkcol

-- same for C table

Set identity_insert OFF A

Commit Tran

- Sekar

super_julius
Starting Member

2 Posts

Posted - 2004-04-29 : 11:26:02

Thanks Sekar for your answer. I will definitely look at your suggestion.

However is there anyway to do it without having to alter the table?

I was thinking of a DTS script that would allow me to do so?

Also I have been using a Data Driven Query so far. Is it the right DTS object to use?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources