public string SelectByKeys(IEnumerable<Row> rows) { var tableName = _connection.TableVariable ? "@KEYS" : "keys_" + _entity.Name; var noCount = _connection.NoCount ? "SET NOCOUNT ON;\r\n" : string.Empty; var sql = noCount + _connection.TableQueryWriter.WriteTemporary(_connection, tableName, _key, false) + SqlTemplates.BatchInsertValues(50, tableName, _key, rows, _connection) + Environment.NewLine + SqlTemplates.Select(_entity.Fields, _entity.Name, tableName, _connection, _entity.Schema, string.Empty) + (_connection.TableVariable ? string.Empty : string.Format("DROP TABLE {0};", tableName)); Debug(sql); return sql; }
private string PrepareSqlWithInputKeys() { const string sqlPattern = @" {0} SELECT e.{1}, e.TflKey{2} FROM {3} e WITH (NOLOCK) INNER JOIN @KEYS k ON ({4}); "; var builder = new StringBuilder(); builder.AppendLine(_connection.WriteTemporaryTable("@KEYS", _key.WithInput())); builder.AppendLine(SqlTemplates.BatchInsertValues(50, "@KEYS", _key.WithInput(), _entity.InputKeys, _connection)); var selectKeys = new FieldSqlWriter(_entity.PrimaryKey).AddDeleted(_entity).Alias(_connection.L, _connection.R).Write(", e.", false); var joinKeys = new FieldSqlWriter(_entity.PrimaryKey).Input().Alias(_connection.L, _connection.R).Set("e", "k").Write(" AND "); return(string.Format(sqlPattern, builder, selectKeys, PrepareVersion(), _connection.Enclose(_entity.OutputName()), joinKeys)); }
public void TestKeyInserts() { var entity = _process.Entities.First(); _process.OutputConnection.IsReady(); var rows = TestOperation(_entityKeysExtract.Object); Assert.AreEqual(4, rows.Count); var actual = SqlTemplates.BatchInsertValues(2, "@KEYS", entity.PrimaryKey, rows, _process.OutputConnection); const string expected = @" INSERT INTO @KEYS SELECT 1 UNION ALL SELECT 2; INSERT INTO @KEYS SELECT 3 UNION ALL SELECT 4;"; Assert.AreEqual(expected, actual); }