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."); }