Comparing massive amountdata between 2 tables on 2 databases

Question

Hi. I have 2 similar tables on two different databases. Lets call them A and B. I want to make a comparison and transfer all rows from A.table to B.table where there is a difference in the "value" column.

 

I have the following code where a loop though a datareader and for each row call a stored procedure that compare the rows.

 

My problem is not that the code does not word, because it does. The problem is that it takes about 10 minutes to complete about 300 rows and I have about 16 million rows to go through.

 

 

The C# code:

_____________________________________________ 

 

OdbcConnection connODBC = new OdbcConnection(strConnStringFrom);

OdbcCommand commandODBC = new OdbcCommand(strQuery, connODBC);

using (connODBC)

{

connODBC.Open();

using (OdbcDataReader reader = commandODBC.ExecuteReader())

{

using (SqlConnection con = new SqlConnection(strConnStringTo))

{

using (SqlCommand cmd = new SqlCommand(strPostSPDBFrom, con))

{

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@project_id", SqlDbType.Int);

cmd.Parameters.Add("@event_id", SqlDbType.Int);

cmd.Parameters.Add("@record", SqlDbType.VarChar);

cmd.Parameters.Add("@field_name", SqlDbType.VarChar);

cmd.Parameters.Add("@value", SqlDbType.VarChar);

cmd.Parameters.Add("@instance", SqlDbType.SmallInt);

con.Open();

while (reader.Read())

{

cmd.Parameters["@project_id"].Value = reader["project_id"];

cmd.Parameters["@event_id"].Value = reader["event_id"];

cmd.Parameters["@record"].Value = reader["record"];

cmd.Parameters["@field_name"].Value = reader["field_name"];

cmd.Parameters["@value"].Value = reader["value"];

cmd.Parameters["@instance"].Value = reader["instance"];

cmd.ExecuteNonQuery();

}

}

}

}

}

 _____________________________________________

 

The Stored Procedure:

 _____________________________________________  

 

 

ALTER PROCEDURE [dbo].[sp]

@project_id AS INT = NULL,

@event_id AS NVARCHAR(100) = NULL,

@record AS VARCHAR(100) = NULL,

@field_name AS VARCHAR(100) = NULL,

@value AS VARCHAR(MAX) = NULL,

@instance AS SMALLINT = NULL

AS

DECLARE @valueTemp varchar(MAX);

Set @valueTemp = (select value from B.table where project_id = @project_id

and event_id = @event_id

and record = @record

and field_name = @field_name

and value = @value

and instance = @instance)

IF @valueTemp IS NULL

BEGIN

insert into B.table (project_id, event_id, record, field_name, value, instance)

values (@project_id, @event_id, @record, @field_name, @value, @instance)

END

ELSE If @valueTemp <> @value

BEGIN

update B.table SET value = @value

where project_id = @project_id

and event_id = @event_id

and record = @record

and field_name = @field_name

END

_____________________________________________  

 

 

Can anybody help me?

 

Regards Thomas