how to tackle the incremental ID

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)

how to tackle the incremental ID

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-07-15 : 14:11:11

I have an ActiveX script to migrate the data and the source data is join of different tables.

Function Main()

DTSDestination("Spike_id")---> This is an incremental value and source doesn't have any value. so I have to go on increment it.Normally it is suppose to increment automatically when u insert but its not working and gives me an error.So i have to get the max of the spike_id and then add one to it.So please help me how to increment this value

DTSDestination("emp_id") = DTSSource("emp_id")
DTSDestination("Dept") = DTSSource("Dept_System_default")
DTSDestination("loan_amount") = DTSSource("amount")
DTSDestination("Entry_dateoriginal_upb")= DTSSource("original_date")
DTSDestination("interest_rate") = DTSSource("interest_rate")
DTSDestination("created_by") ="Admin"
DTSDestination("create_date") =Date

Main = DTSTransformStat_OK

End Function

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-15 : 14:14:25

Use a staging table that does not have the incremental id column. Then:

INSERT INTO RealTableName (Column2, Column3, Column4)
SELECT Column2, Column3, Column4
FROM StagingTable

I have left out Column1 in RealTableName as that would be the identity column.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-07-15 : 15:23:59

The destination table is in different server and at the last step I will have to use the Dts right.I have a staging table where i can insert and now i need to transfer this into the real production using dts

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-15 : 15:57:57

Then use a linked server for the query:

INSERT INTO LinkedServerName.DBName.dbo.RealTableName (Column2, Column3, Column4)
SELECT Column2, Column3, Column4
FROM StagingTable

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources