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