Identity Column Problem in MS SQL Server

[asifjavaid]

Hi all,:confused:
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
------------------------------------------------

waiting for ur responce. i will very thankful to you by giving me effecient and excellent solution.
Thanks....
God Bless u By all means.;)

[MCP]

How are you moving the data? Through SQL statements? Through the EM Import Wizard?

Anyhow, the answer is enabling "identity insert" in your target table B. This option can only be active for one table at any given moment, and is usually turned on, data copied, then turned off again.

I'd argue though that the id field in table B shouldn't be an identity at all. You can still make it a primary key, but it's not really an identity field.

Without knowing more about your DB's design, it's hard to say though.

[asifjavaid]

Hi all,:confused:
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
------------------------------------------------

waiting for ur responce. i will very thankful to you by giving me effecient and excellent solution.
Thanks....
God Bless u By all means.;)

***********************************************************
***********************************************************
***********************************************************
date :- Thursday, October 19, 2006
Hi all dear:)
Thank you so much in responce to much problem.....

Dear friend,
in responce to reply, i am saying that your solution is very good for inserting one statement but i am trying to move data from table "A" to table "B" using Bulk copying. My dear now tell me what will be the solution in case of Bulk copy....?????

I hope to get an excellent responce from you side....
very very thanks.
God Bless u By all means:) ;)
Asif Javaid