Identity Column Problem in MS SQL Server when moving data from using Bulk Copy

asifjavaid's Avatar, Join Date: Oct 2006
Newbie Member
Hi all,

God Bless U,

i am facing a problem in SQL Server. I am have a table given below..
------------------------------------------------
Table name is "A"
------------------------------------------------
1 Michle Administrator
2 John Consumer Finance Officer
3 Jackson Employer
4 Goeffery Empl0yer

------------------------------------------------
Here the identity column is the first one. Now if i delete the 2nd row the this table becomes like that
------------------------------------------------
Table name is "A"
------------------------------------------------
1 Michle Administrator
3 Jackson Employer
4 Goeffery Empl0yer

------------------------------------------------

Now i have an another table "B" having same structure (3 columns and first column is the identity column). Now if i move the all the data from table "A" to table "B" then the original seqeunce is distrubed the table "B" contains the data like this.


------------------------------------------------
Table name is "B"
------------------------------------------------
1 Michle Administrator
2 Jackson Employer
3 Goeffery Empl0yer

------------------------------------------------


but i need that what ever the first column is identity column but sequence of numbering must be same as the numbering in the table "A" to preserve the relationships.... like that...


------------------------------------------------
Table name is "B"
------------------------------------------------
1 Michle Administrator
3 Jackson Employer
4 Goeffery Empl0yer

------------------------------------------------
Notice :- Make i am moving data from Table "A" to Table "B" using Bulk Copying.. The problem is facing using Bulk Copy. in case of only one statement their no problem, i have a solution for that but i not for bulk copy case.

waiting for a good responce from ur side . i will very thankful to the friend who solve my problem and give me an efficient solution .
Thanks....
God Bless u By all means.

Asif Javaid
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
You have posted it as an Article under the Article / Source code section. I have moved it to the Queries and Discussion forum.

The identity column need not be auto generated in the destination column of the copying table.
sunflowerhot's Avatar, Join Date: Jun 2007
Newbie Member
vow
venkatesanj@hcl.in's Avatar
Go4Expert Member
Yes exactly instead of copying using bulk copy try to use insert statement to insert the rows dropping the identity contraint on that particular column after that you can make it as an identity column.