20230128

SQL作成

//KOUSHINBI → UPDATE REPRACE
//差分抽出方法を変更
//■MRGでUPDATEとINSERTを実行する
public void ComFunc()
{
//全件数カウント
chkCNT = CNT(repTN,xbbTN);

//MARGE実行(差分抽出→項目更新→日付更新)
MRG(repTN, xbbTN, colNum, chkCNT);

//削除
DEL(repTN, xbbTN, whereDEL_R, whereDEL_X);

TableMinus_CNT();
}

public int CNT(string repTN, string xbbTN)
{
DataTable dt = new DataTable();

Console.WriteLine("■件数カウント開始");

//複製テーブル全件数
strSql = "select count(*) from " + repTN; ;
dt = DBA(strSql); //SQL実行
Rcount = dt.Rows[0][0].ToString();
Console.WriteLine(repTN + " 全" + Rcount + "行");

//元テーブル全件数
strSql = "select count(*) from " + xbbTN; ;
dt = DBA(strSql); //SQL実行
Xcount = dt.Rows[0][0].ToString();
Console.WriteLine(xbbTN + " 全" + Xcount + "行\r\n");

if (Rcount == Xcount)
{
Console.WriteLine("行数一致\r\n");
}
else
{
Console.WriteLine("行数差分あり\r\n");
chkCNT = 1;
}
return chkCNT;
}

public void MRG(string repTN, string xbbTN, int colNum, int chkCNT)
{
DataTable dt = new DataTable();
int i = 1;

Console.WriteLine("■MAREGE開始");

//差分抽出
dt = TableMinus();

if (dt.Rows.Count >= 1 || chkCNT == 1)
{
Console.WriteLine("実行...\r\n");

strSql_MRG =
"merge into " + repTN +
" using " + xbbTN +
" on (" + whereON + ")" +
" when matched then update set ";

string strSql_MRG2 =
" where R.KOUSHINBI < X.KOUSHINBI" +
" when not matched then insert (";

string strSql_MRG3 = " values (";

foreach (DataColumn dc in dt.Columns)
{
switch (colNum)
{
case 1:
if (dc.ColumnName == "KOUSHINBI" || dc.ColumnName == col1)
{
Console.WriteLine(i + ":" + dc.ColumnName + " ON句条件は更新不可");

if (i == dt.Columns.Count)
{
strSql_MRG2 = strSql_MRG2 + "R." + dc.ColumnName;
strSql_MRG3 = strSql_MRG3 + "X." + dc.ColumnName;
}
else
{
strSql_MRG2 = strSql_MRG2 + "R." + dc.ColumnName + ",";
strSql_MRG3 = strSql_MRG3 + "X." + dc.ColumnName + ",";
}
}

else
{
Console.WriteLine(i + ":" + dc.ColumnName + " 更新実行...");

if (i == dt.Columns.Count)
{
strSql_MRG = strSql_MRG + "R." + dc.ColumnName + " = X." + dc.ColumnName;
strSql_MRG2 = strSql_MRG2 + "R." + dc.ColumnName;
strSql_MRG3 = strSql_MRG3 + "X." + dc.ColumnName;
}
else
{
strSql_MRG = strSql_MRG + "R." + dc.ColumnName + " = X." + dc.ColumnName + ",";
strSql_MRG2 = strSql_MRG2 + "R." + dc.ColumnName + ",";
strSql_MRG3 = strSql_MRG3 + "X." + dc.ColumnName + ",";
}

}
break;

case 2:
if (dc.ColumnName == "KOUSHINBI" || dc.ColumnName == col1 || dc.ColumnName == col2)
{
Console.WriteLine(i + ":" + dc.ColumnName + " 更新不可\r\n");
}

else
{
Console.WriteLine(i + ":" + dc.ColumnName + " 更新実行...\r\n");
}
break;
}
i++; //次の列へ
}

strSql = strSql_MRG + strSql_MRG2 + ")" + strSql_MRG3 + ")";
Console.WriteLine(strSql + "\r\n");
dt = DBA(strSql);

//日付(KOUSHINBI)を更新
strSql_MRG =
"merge into " + repTN +
" using " + xbbTN +
" on (" + whereON + ")" +
" when matched then update set" +
" R.KOUSHINBI = X.KOUSHINBI where R.KOUSHINBI < X.KOUSHINBI";

strSql = strSql_MRG;
Console.WriteLine(strSql + "\r\n");
dt = DBA(strSql);
}

else
{
Console.WriteLine("更新なし\r\n");
}

}

public void DEL(string repTN, string xbbTN, string whereDEL_R, string whereDEL_X)
{
DataTable dt = new DataTable();
int i = 1;

Console.WriteLine("■DEL開始");

//差分抽出
//dt = TableMinus();

if (chkCNT == 1)
{
Console.WriteLine("実行...\r\n");

strSql_DEL =
"delete from " + repTN +
" where (" + whereDEL_R + ")" +
" not in (select " + whereDEL_X + " from " + xbbTN + ")";

strSql = strSql_DEL;
Console.WriteLine(strSql + "\r\n");
dt = DBA(strSql);
}

else
{
Console.WriteLine("削除なし\r\n");
}

}

public DataTable TableMinus_CNT()
{
string repTN = "DEPT_BK DBK";
string xbbTN = "DEPT D";

DataTable dt = new DataTable();

Console.WriteLine("差分抽出開始...\r\n");

strSql_MINUS =
"select count(*) from (select * from " + repTN +
" minus select * from " + xbbTN + ")";

strSql = strSql_MINUS;
dt = DBA(strSql); //SQL実行

Console.WriteLine(dt.Rows[0][0] + "行の差分あり\r\n");

return dt;
}