Sequence number column in destination table not in source table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Sequence number column in destination table...

Sequence number column in destination table not in source table

Rate Topic

Display Mode

Topic Options

Author

Message

ronsql

Posted Monday, March 16, 2009 3:59 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:03 AM
Points: 20, Visits: 382

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.

Post #677015

Phil Parkin

Posted Monday, March 16, 2009 7:32 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 4,235, Visits: 9,472

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.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #677108

ronsql

Posted Monday, March 16, 2009 9:15 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:03 AM
Points: 20, Visits: 382

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.

Post #677138

Phil Parkin

Posted Monday, March 16, 2009 9:40 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 4,235, Visits: 9,472

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

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #677149

ronsql

Posted Tuesday, March 17, 2009 6:55 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:03 AM
Points: 20, Visits: 382

yes,how should i create an ssis package.

give me any example with sample code.

thanks.

Post #677389

Phil Parkin

Posted Tuesday, March 17, 2009 7:29 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 4,235, Visits: 9,472

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

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #677424

ronsql

Posted Tuesday, March 17, 2009 8:59 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:03 AM
Points: 20, Visits: 382

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

canu make it clear.

Thanks.

Post #677557

« Prev Topic | Next Topic »

Permissions