CODEWITHSUNDEEP
c#ms-accesssql-server-2008-r2database-schema
faheem khan
faheem khan

Reputation: 481

Copy from source sql database to destination sql database

I have to copy data from one SQL Server database to other SQL Server database.

There are columns such as Identity and other data types that exist in destination SQL Server DB but not in source database. Source table has only term column, but destination table has id (identity) and term column.

During copy I am getting error like:

Explicit value must be specified for identity column in table 'Terms' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

string colNames = "";
foreach (DataColumn col in ds.Tables[0].Columns)
{
    colNames += col.ColumnName + ",";
}
colNames = colNames.Remove(colNames.Length - 1, 1);
sqlCmd2.Parameters.Clear();

bool hasIdentityCol = false;
sqlCmd2.CommandType = CommandType.Text;


sqlCmd2.CommandText = "SELECT OBJECTPROPERTY(object_id('" + row["Table_Name"] + "'), 'TableHasIdentity')";
if (sqlCmd2.ExecuteScalar() != DBNull.Value && Convert.ToInt32(sqlCmd2.ExecuteScalar()) > 0)
{
    hasIdentityCol = true;
}


if (hasIdentityCol == true)
{
    sqlCmd2.CommandText = "SET IDENTITY_INSERT  dbo." + row["Table_Name"] + " ON";
    sqlCmd2.Transaction = sqlTran;
    sqlCmd2.ExecuteNonQuery();
}

sqlCmd2.CommandText = "INSERT INTO   " + txtDestinationDatabase.Text + ".dbo." + row["Table_Name"] + "(" + colNames + ")  SELECT " + colNames + " FROM " + txtSourceDatabase.Text + ".dbo." + row["Table_Name"];
sqlCmd2.Transaction = sqlTran;
sqlCmd2.ExecuteNonQuery();
if (hasIdentityCol == true)
{
    sqlCmd2.CommandText = "SET IDENTITY_INSERT " + txtDestinationDatabase.Text + ".dbo." + row["Table_Name"] + " OFF";
    sqlCmd2.Transaction = sqlTran;
    sqlCmd2.ExecuteNonQuery();
}

Upvotes: 0

Views: 379

Answers (1)

Sebastian Meine
Sebastian Meine

Reputation: 11773

If you turne IDENTITY_INSERT on you have to specify values for the identity column. You say that your source table does not have a column that could be copied into the identity column. To make this work, just remove the SET IDENTITY_INSERT statements and only copy the columns you actually have.

If there are additional columns that are not nullable, you might have to hard-code values for those.

Upvotes: 1

Related Questions