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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.