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