AsifJavaid


hi all,

i am beginner in .NET visual programming using C#.NET with SQL Server 2005

i am facing a problem in SQL Server. suppose I 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 to solve my problem.

Note:- the is moved using SQLBULKCOPY command.....


Thanks....
God Bless u By all means.





Re: SQL BULK Copy Problem using C#.NET 2.0

Aaronru - MSFT


Moving to a more appropriate forum.




Re: SQL BULK Copy Problem using C#.NET 2.0

carlop

You have to set the identity_insert property on:

-- set identity insert on
SET IDENTITY_INSERT mytable2 ON
GO

-- now perform the bulk insert
select id, name, ... into mytable2 from mytable1 where ...

-- set identity insert off
SET IDENTITY_INSERT mytable OFF
GO