Copying a table in ADO.net and the pesky identity column

[Rippo]

I am finding problems in trying to copy a databse table from one SQL
2000 server to another using ADO.net. However the source table has an
identity column and I need to preserve the values in the destination
table. Obviously every time I run the below code the destination table
does not have the correct identity column.

For Each objDataRow In dsSource.Tables(0).Rows

With dsDest.Tables(0)

objDBRow = .NewRow()
For Each c In .Columns
objDBRow(c.ColumnName) = objDataRow(c.ColumnName)
Next
.Rows.Add(objDBRow)
End With

ObjCmdBuilder = New SqlCommandBuilder(objAdapter)
objAdapter.Update(dsDest, tablename)

Next

What is the best approach in acheiving this? Bear in mind that the two
SQL servers cannot talk to each other so that I cannot use
sp_addlinkedserver and use a stored procedure.

Am I going about this incorrectly?

Thanks Rippo

 

[Chris Taylor]

Hi,

If you want to insert an explicit value into an identity column, you can
issue SET IDENTITY INSERT ON statement on the connection before performing
the inserts. Take a look in books online for the exacty syntax and
restriction.

Hope this helps

 

[Rippo]

Chris

Ok here is what I have:-

objAdapter = New SqlDataAdapter("SET IDENTITY_INSERT tblScreens ON;
SELECT * FROM tblScreens WHERE 1 = 2 ", cnMainLocal)

objAdapter.Fill(objDSDBTable, tablename)

For Each objDataRow In dsSource.Tables(0).Rows
With dsDest.Tables(0)

objDBRow = .NewRow()
For Each c In .Columns
objDBRow(c.ColumnName) = objDataRow(c.ColumnName)
Next
.Rows.Add(objDBRow)
End With

ObjCmdBuilder = New SqlCommandBuilder(objAdapter)
objAdapter.Update(dsDest, tablename)

Next

However I get the following error message when i perform the
objAdapter.Update(objDSDBTable, tablename):-

Explicit value must be specified for identity column in table
'tblScreens' when IDENTITY_INSERT is set to ON

This means that the columns need to be Explicitly defined but the update
does not appear to send the column names.

Can you provide further information?
Many thanks
Rippo

 

[Jim Hughes]

You can't use SELECT *, you must specify each column name.

You also need to SET IDENTITY_INSERT tblScreens OFF when you are done as it
can only be set on one table at a time, and will fail (I believe) if you try
to turn it on a table for which it is already on.

You probably won't be able to use the CommandBuilder either, because it
won't use the identity column for the insert. Set the InsertCommand for the
dataadapter in code.

Try it from Query Analyzer before you try to code it.

 

[Richard Wilde]

Thanks for that.

However I will be using this code to copy over a lot of tables and did not
want to hard code the field names as i have this outer loop

For Each objDataRow1 In dsDataForXML.Tables(0).Rows
tablename = objDataRow1.Item("tablename")

... other code already poste below....
next

Is there a another way to achieve this

Thanks

 

[Corbin]

This may seem impertinent, but is there a reason you are using ADO to
do this? It sounds like DTS or bcp would be better suited to the task.