Sequence number column in destination table not in source table

Question

Sequence number column in destination table not in source table

ronsql

Old Hand

Points: 382
More actions
March 16, 2009 at 3:59 pm

#129097

Sequence number column in destination table not in source table.
I have an issue when i m migrating the data from sqlserver to sqlserver database.
the column seq number is in destination table(not in source table) which can write the records sequentially at the time of inserting the data in to destation table.
Please give any sample code for this.
what is tyhe approach for this.
Reply ASAP.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Is the sequence number arbitrary? If so, why not make it an IDENTITY(1,1) field in the destination table and then SQL Server will take care of the numbering for you.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ronsql Old Hand Points: 382 More actions · Answer 2

There is sequence number column in destination table(there is no mapping filed in source table).When update or insert the data in to destination table the sequence number field should be in destination table along with sequence number which record(increments based no of times the record is updated) is updated and inserted in to destination table.

Thanks.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

So a sequence number of 8 means the record was INSERTED once and updated seven times (1 + 7 = 8)?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ronsql Old Hand Points: 382 More actions · Answer 4

yes,how should i create an ssis package.

give me any example with sample code.

thanks.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Hmmm, you really need an example for this?

Something along the following lines (untested & I'm getting tired):

INSERT INTO DestTable(Field1, Fieldn, SeqNo)

SELECT S.Field1, S.Fieldn, 1

FROM SourceTable S

WHERE [Source record not in Destination table]

UPDATE DestTable

SET Field1 = S.Field1, Fieldn = S.Fieldn, SeqNo = SeqNo + 1

FROM SourceTable S

WHERE S.Keyfield = DestTable.Keyfield

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ronsql Old Hand Points: 382 More actions · Answer 6

I m little bit confused with the code which u sent as an example.

canu make it clear.

Thanks.