Database Insert with IDENTITY ON

  • Thread starter Thread starter shootsie
  • Start date Start date
S

shootsie

I have a situation where I have two datasets each equal with the data
from the same table. I want to update one dataset with the records
from the next dataset IDs in tact. Here's the code I have:


' Set AcceptChangesDuringFill = False
objDataAdapterSource.AcceptChangesDuringFill = False
' Fill the data adapter with some data from the source "myTable"
objDataAdapterSource.Fill(objDataSetSource, "myTable")
' This is a function I wrote that just calls the "SET
' IDENTITY_INSERT myTableON"
SetIdentityInsert(True, "myTable")
' Select everything from myTable on the database toupdate and
' create a data adapter
strSQL = "SELECT * FROM myTable"
objDataAdapterDest= New SqlClient.SqlDataAdapter(strSQL, strConn)
' create a commandbuilder object
objCB = New SqlClient.SqlCommandBuilder(objDataAdapterDest)'
'Update the destination data set
objDataAdapterDest.Update(objDataSetSource, "myTable")


Let's say the Source object objDataSetSource had a "myTable" with two
columns" "TableID" and "Description" where "TableID" was the key.
Let's say it had one row, where the TableID = 1 and Description =
"TEST". I am expecting the same table in the destination database to
be inserted with the exact same data. Instead, the TableID ends up
being generated by the database as if Identity_Insert isn't turned on.
Any suggestions?
 
If I were you, I would move away from default Update of the DataSet and write
an UPDATE stored procedure that could set IDENTITY INSERT ON for you. Wrap in
a transaction so you can ensure it is off if there is an error in the
process. Allowing the DataAdapter to create an INSERT command is dangerous
when you are messing with IDENTITY values.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Back
Top