private void ScriptTableMerge(ScriptObject so, DataTable colInfoTable, DataTable mainTable, System.IO.StreamWriter w, string colList, string scolList, string PKcolListOn, string colCastList)
{
string tmpTableName = so.TempCounterpart;
bool useIdentity = hasIdentity(colInfoTable);
w.WriteLine(String.Format("IF OBJECT_ID('tempdb.dbo.{0}') IS NOT NULL DROP TABLE {0};", tmpTableName));
w.WriteLine(String.Format("SELECT * INTO {1} FROM {0} WHERE 0=1;", so.FullQuoted, tmpTableName));
w.WriteLine(_BatchSeparator);
if (useIdentity)
{
w.WriteLine(String.Format("SET IDENTITY_INSERT {0} ON;", tmpTableName));
w.WriteLine(_BatchSeparator);
}
string rowsep = "\t ";
int rowIndex = 0;
foreach (DataRow row in mainTable.Rows)
{
//Begin Insert to temp
if (rowIndex % MaxRowsPerBatch == 0)
{
w.WriteLine(String.Format("INSERT INTO {0} ", tmpTableName));
w.WriteLine(String.Format(" ({0})", colList));
w.WriteLine(String.Format("SELECT {0} FROM ", colCastList));
w.WriteLine("(VALUES");
rowsep = "\t ";
}
//VALUES
w.Write(rowsep);
rowsep = "\t, ";
w.WriteLine(SerializeRowValues(row, colInfoTable, "(", ")"));
rowIndex++;
//End Insert to Temp
if (rowIndex % MaxRowsPerBatch == 0 || rowIndex == mainTable.Rows.Count)
{
w.WriteLine(String.Format(") as v ({0});", colList));
w.WriteLine(_BatchSeparator);
w.WriteLine("");
}
}
//Identity
if (useIdentity)
{
w.WriteLine(String.Format("SET IDENTITY_INSERT {0} OFF;", tmpTableName));
w.WriteLine(_BatchSeparator);
w.WriteLine(String.Format("SET IDENTITY_INSERT {0} ON;", so.FullQuoted));
w.WriteLine(_BatchSeparator);
}
//Begin Merge
w.WriteLine("");
w.WriteLine(String.Format("WITH cte_data as (SELECT {1} FROM [{0}])", tmpTableName, colCastList));
w.WriteLine(String.Format("MERGE {0} as t", so.FullQuoted));
w.WriteLine("USING cte_data as s");
w.WriteLine(String.Format("\tON {0}", PKcolListOn.Replace(",", " AND")));
w.WriteLine("WHEN NOT MATCHED BY target THEN");
w.WriteLine(String.Format("\tINSERT ({0})", colList));
w.WriteLine(String.Format("\tVALUES ({0})", scolList));
//Merge: WHEN MATCHED
if (so.Method != "MERGE_NEW_ONLY")
{
string UpdateColList = GetColList(colInfoTable, "constraint_type IS NULL", "{0} = s.{0}");
w.WriteLine("WHEN MATCHED THEN ");
w.WriteLine("\tUPDATE SET ");
w.WriteLine(String.Format("\t{0}", UpdateColList));
}
//Merge: WHEN NOT MATCHED
if (so.Method == "MERGE")
{
w.WriteLine("WHEN NOT MATCHED BY source THEN");
w.WriteLine("\tDELETE");
}
w.WriteLine(";");
w.WriteLine(_BatchSeparator);
//End
if (useIdentity)
{
w.WriteLine(String.Format("SET IDENTITY_INSERT {0} OFF;", so.FullQuoted));
w.WriteLine(_BatchSeparator);
}
w.WriteLine(String.Format("DROP TABLE {0};", tmpTableName));
w.WriteLine(_BatchSeparator);
}
public void GenerateForTable(ScriptObject so)
{
_BatchSeparator = this.OptionProcWrapUp ? "" : "GO";
DataTable colInfoTable = LoadColumnsInfo(so);
DataTable mainTable = _db.SelectTable(getSelectStatement(colInfoTable, so), "MainTable");
string filename = Path.Combine(_OutputFolder, so.FullName + ".sql");
System.IO.StreamWriter w = new System.IO.StreamWriter(filename);
string colList = GetColList(colInfoTable, "", "{0}");
string colCastList = GetColListWithCastForPK(colInfoTable);
string scolList = GetColList(colInfoTable, "", "s.{0}");
string PKcolListOn = GetColList(colInfoTable, "constraint_type = 'PRIMARY KEY'", "t.{0} = s.{0}");
//SP
if (OptionProcWrapUp)
{
w.WriteLine(String.Format("CREATE PROCEDURE [{0}].[Populate_{1}]", "dbo", so.TempCounterpart.Replace("#", "")));
w.WriteLine("AS");
w.WriteLine("BEGIN");
}
//Header
w.WriteLine("/*");
w.WriteLine(String.Format("\tTable's data: {0}", so.FullQuoted));
w.WriteLine(String.Format("\tData Source: [{0}].[{1}]", ServerName, DbName));
w.WriteLine(String.Format("\tCreated on: {0}", DateTime.Now));
w.WriteLine(String.Format("\tScripted by: {0}", UserName));
w.WriteLine("\tGenerated by " + Global.AppName);
w.WriteLine("*/");
//Body
w.WriteLine(String.Format("PRINT 'Populating data into {0}';", so.FullQuoted));
w.WriteLine(_BatchSeparator);
switch (so.Method)
{
case "MERGE":
case "MERGE_without_DELETE":
case "MERGE_NEW_ONLY":
ScriptTableMerge(so, colInfoTable, mainTable, w, colList, scolList, PKcolListOn, colCastList);
break;
case "INSERT":
ScriptTableInitialInsert(so, colInfoTable, mainTable, w, colList, scolList, PKcolListOn);
break;
default:
throw new Exception("Unknown method: " + so.Method);
}
//Footer
w.WriteLine(String.Format("-- End data of table: {0} --", so.FullQuoted));
if (OptionProcWrapUp)
{
w.WriteLine("END");
w.WriteLine("GO");
}
w.Close();
Console.WriteLine(so.FullQuoted + " was scripted.");
}
