trying to keep the origional identity in sql bulk transfer

I am trying to preserve the origional Identity how do I do that - any ideas?  here is my code
using (var sourceConnection = new MySqlConnection(connectionString))
            {
                sourceConnection.Open();
                var commandSourceData = new MySqlCommand("SELECT * from " + table + ";", sourceConnection);
                var reader = commandSourceData.ExecuteReader();
                using (var destinationConnection = new SqlConnection(connectionStringDest))
                {
                    destinationConnection.Open();

                    using (var bulkCopy = new SqlBulkCopy(destinationConnection))
                    {
                        if (table == "BatteryData")
                        {
                            
                            bulkCopy.ColumnMappings.Add("ID", "ID");
                            bulkCopy.ColumnMappings.Add("UploadTime", "UploadTime");
                            bulkCopy.ColumnMappings.Add("Location", "Location");
                            bulkCopy.ColumnMappings.Add("SerialNumber", "SerialNumber");
                            bulkCopy.ColumnMappings.Add("GaugeSerialNumber", "GaugeSerialNumber");
                            bulkCopy.ColumnMappings.Add("BatteryType", "BatteryType");
                            bulkCopy.ColumnMappings.Add("BatteryUsed", "BatteryUsed");
                            bulkCopy.ColumnMappings.Add("BatteryVoltage", "BatteryVoltage");
                            bulkCopy.ColumnMappings.Add("BatteryManufacturer", "BatteryManufacturer");
                            bulkCopy.ColumnMappings.Add("BatteryRetired", "BatteryRetired");
                            bulkCopy.ColumnMappings.Add("DefaultLocationIndex", "DefaultLocationIndex");
                         
                        }
                        
                        bulkCopy.DestinationTableName = "[" + table + "]";
                        try
                        {
                            DeleteFrom(table);
                           
                            bulkCopy.WriteToServer(reader);
                           
                            label1.Visible = true;
                            label1.Text = table + " copied";
                            
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                        finally
                        {

                            reader.Close();
                        }
                    }
                }

Open in new window

LVL 6
r3nderAsked:
Who is Participating?
 
PatHartmanCommented:
That's is strange.  The SET IDENTITY_INSERT isn't language dependent.  It is a command passed through to the server.  I wonder if your problem is related to the ODBC driver you are using or some database level setting that prevents turning this property on.
0
 
r3nderAuthor Commented:
i tried this but it didnt work - iit said some perameters were wrong
  using (var sourceConnection = new MySqlConnection(connectionString))
            {
                sourceConnection.Open();
                var commandSourceData = new MySqlCommand("SELECT * from " + table + ";", sourceConnection);
                var reader = commandSourceData.ExecuteReader();
                using (SqlConnection destinationConnection = new SqlConnection(connectionStringDest))
                {
                    destinationConnection.Open();

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.KeepIdentity))
                    {
                        if (table == "BatteryData")
                        {
}
0
 
nishant joshiTechnology Development ConsultantCommented:
Hi,

The only way is create view from our table exclude that identity column from it and use bulk copy on view.

Thanks,
Nishant
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
r3nderAuthor Commented:
I got it to accept this but the identity is still not working
            using (var sourceConnection = new MySqlConnection(connectionString))
            {
                sourceConnection.Open();
                var commandSourceData = new MySqlCommand("SELECT * from " + table + ";", sourceConnection);
                var reader = commandSourceData.ExecuteReader();
                using (SqlConnection destinationConnection = new SqlConnection(connectionStringDest))
                {

                    destinationConnection.Open();
                    SqlCommand commandDelete = new SqlCommand();
                    commandDelete.Connection = destinationConnection;
                    commandDelete.CommandText =
                        "DELETE FROM " + table + "";
                    commandDelete.ExecuteNonQuery();
                   
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStringDest, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls))
                    {
                        //bulkCopy.BulkCopyTimeout = 0;
                        if (table == "BatteryData")
                        {
                         }

Open in new window

0
 
PatHartmanCommented:
I'm not sure what language you are writing in but here is VBA code that does what you need written in an Access application.  The app has the option of being linked to Jet/ACE or SQL Server so the code has to accommodate both.  Here it checks for a connect string.  If it is null, it is assumed to be Jet/ACE.  Otherwise, it is assumed to be SQL Server.  The code sets Identity_Insert off and on as necessary.
    If IsNull(strConnect) Then
        DoCmd.OpenQuery "qRESET2AppendMembers", acNormal, acEdit
        DoCmd.OpenQuery "qRESET3AppendDependents", acNormal, acEdit
    Else
        'when tables are linked SQL, the identity insert must be set to on
        'to allow rows with existing autonumbers to be inserted and then off at the end.
        'We do this so that the test data always retains its original autonumbers.
        Set db = CurrentDb()
        Set qd = db.CreateQueryDef("")
            qd.Connect = "ODBC;" & strConnect
            qd.ReturnsRecords = False
            qd.SQL = "SET IDENTITY_INSERT " & "tblMembers" & " ON"
            qd.Execute
        DoCmd.OpenQuery "qRESET2AppendMembers", acNormal, acEdit
            qd.SQL = "SET IDENTITY_INSERT " & "tblMembers" & " OFF"
            qd.Execute
            qd.SQL = "SET IDENTITY_INSERT " & "tblDependents" & " ON"
            qd.Execute
        DoCmd.OpenQuery "qRESET3AppendDependents", acNormal, acEdit
            qd.SQL = "SET IDENTITY_INSERT " & "tblDependents" & " OFF"
            qd.Execute
            qd.Close
            db.Close
    End If

Open in new window

0
 
r3nderAuthor Commented:
I tried that myself - didnt work and it is C#

        private void bulktransferdata(string table)
        {
            label1.Visible = false;
            label1.Text = "";
            //source is mysql
            using (var sourceConnection = new MySqlConnection(connectionString))
            {
                //opoen source
                sourceConnection.Open();
                //select form mysqql tables
                var commandSourceData = new MySqlCommand("SELECT * from " + table + ";", sourceConnection);
                var reader = commandSourceData.ExecuteReader();
                //set up the destination connection
                using (SqlConnection destinationConnection = new SqlConnection(connectionStringDest))
                {
                    //open it
                    destinationConnection.Open();
                    //delete from the sql table
                    SqlCommand commandDelete = new SqlCommand();
                    commandDelete.Connection = destinationConnection;
                    commandDelete.CommandText =
                        "DELETE FROM " + table + "";
                    commandDelete.ExecuteNonQuery();
                    //set Identity insert - since the sqlbulk copy wont
                    SqlCommand commandSetInsert = new SqlCommand();
                    commandSetInsert.Connection = destinationConnection;
                    commandSetInsert.CommandText =
                        "SET IDENTITY_INSERT " + table + " ON";
                    commandSetInsert.ExecuteNonQuery();
                    //use bulk copy set it to keep the identity
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStringDest, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls))
                    {
                        //bulkCopy.BulkCopyTimeout = 2000;
                        if (table == "BatteryData")
                        {

                            bulkCopy.ColumnMappings.Add("ID", "ID");
                            bulkCopy.ColumnMappings.Add("UploadTime", "UploadTime");
                            bulkCopy.ColumnMappings.Add("Location", "Location");
                            bulkCopy.ColumnMappings.Add("SerialNumber", "SerialNumber");
                            bulkCopy.ColumnMappings.Add("GaugeSerialNumber", "GaugeSerialNumber");
                            bulkCopy.ColumnMappings.Add("BatteryType", "BatteryType");
                            bulkCopy.ColumnMappings.Add("BatteryUsed", "BatteryUsed");
                            bulkCopy.ColumnMappings.Add("BatteryVoltage", "BatteryVoltage");
                            bulkCopy.ColumnMappings.Add("BatteryManufacturer", "BatteryManufacturer");
                            bulkCopy.ColumnMappings.Add("BatteryRetired", "BatteryRetired");
                            bulkCopy.ColumnMappings.Add("DefaultLocationIndex", "DefaultLocationIndex");

                        }

Open in new window

0
 
nishant joshiTechnology Development ConsultantCommented:
SQL code whould be to create view which not contains Identty column,

CREATE VIEW vw_BatteryData
AS
SELECT
	ID,
	UploadTime,
	Location,
	SerialNumber,
	GaugeSerialNumber,
	BatteryType,
	BatteryUsed,
	BatteryVoltage,
	BatteryManufacturer,
	BatteryRetired,
	DefaultLocationIndex
FROM dbo.BatteryData

Open in new window


then your c# code will be
        private void bulktransferdata(string table)
        {
            label1.Visible = false;
            label1.Text = "";
            //source is mysql
            using (var sourceConnection = new MySqlConnection(connectionString))
            {
                //opoen source
                sourceConnection.Open();
                //select form mysqql tables
                var commandSourceData = new MySqlCommand("SELECT * from vw_" + table + ";", sourceConnection);
                var reader = commandSourceData.ExecuteReader();
                //set up the destination connection
                using (SqlConnection destinationConnection = new SqlConnection(connectionStringDest))
                {
                    //open it
                    destinationConnection.Open();
                    //delete from the sql table
                    SqlCommand commandDelete = new SqlCommand();
                    commandDelete.Connection = destinationConnection;
                    commandDelete.CommandText =
                        "DELETE FROM " + table + "";
                    commandDelete.ExecuteNonQuery();
                    
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStringDest, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls))
                    {
                        //bulkCopy.BulkCopyTimeout = 2000;
                        if (table == "BatteryData")
                        {

                            bulkCopy.ColumnMappings.Add("UploadTime", "UploadTime");
                            bulkCopy.ColumnMappings.Add("Location", "Location");
                            bulkCopy.ColumnMappings.Add("SerialNumber", "SerialNumber");
                            bulkCopy.ColumnMappings.Add("GaugeSerialNumber", "GaugeSerialNumber");
                            bulkCopy.ColumnMappings.Add("BatteryType", "BatteryType");
                            bulkCopy.ColumnMappings.Add("BatteryUsed", "BatteryUsed");
                            bulkCopy.ColumnMappings.Add("BatteryVoltage", "BatteryVoltage");
                            bulkCopy.ColumnMappings.Add("BatteryManufacturer", "BatteryManufacturer");
                            bulkCopy.ColumnMappings.Add("BatteryRetired", "BatteryRetired");
                            bulkCopy.ColumnMappings.Add("DefaultLocationIndex", "DefaultLocationIndex");

                        }

Open in new window


Hoping... above code would definitely work for you and you would have a great lunch..:):)
0
 
r3nderAuthor Commented:
I will  check on that pat and nishant
0
 
nishant joshiTechnology Development ConsultantCommented:
One more comment regarding "set identity_insert on" is dependent on session. Here session will change connection remains same.
0
 
r3nderAuthor Commented:
I ended up doing a bulk transfer
0
 
r3nderAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.