public void InsertStatement_Select()
{
string sql = @"
INSERT INTO dbo.EmployeeSales
SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY sp.BusinessEntityID, c.LastName;" ;
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
sql,
includeWhitespace: false);
TSQLInsertStatement insert = statements[0].AsInsert;
Assert.AreEqual(55, insert.Tokens.Count);
Assert.IsNull(insert.With);
Assert.IsNull(insert.Output);
Assert.IsNull(insert.Values);
Assert.IsNull(insert.Default);
Assert.IsNull(insert.Execute);
Assert.AreEqual(5, insert.Insert.Tokens.Count);
Assert.AreEqual(50, insert.Select.Tokens.Count);
}
public void InsertStatement_DefaultValues()
{
string sql = @"
INSERT INTO T1 DEFAULT VALUES;" ;
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
sql,
includeWhitespace: false);
TSQLInsertStatement insert = statements[0].AsInsert;
Assert.AreEqual(5, insert.Tokens.Count);
Assert.IsNull(insert.With);
Assert.IsNull(insert.Output);
Assert.IsNull(insert.Select);
Assert.IsNull(insert.Values);
Assert.IsNull(insert.Execute);
Assert.AreEqual(3, insert.Insert.Tokens.Count);
Assert.AreEqual(2, insert.Default.Tokens.Count);
}
public void InsertStatement_SimpleValues()
{
string sql = @"
INSERT INTO Production.UnitMeasure
VALUES (N'FT', N'Feet', '20080414');" ;
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
sql,
includeWhitespace: false);
TSQLInsertStatement insert = statements[0].AsInsert;
Assert.AreEqual(13, insert.Tokens.Count);
Assert.IsNull(insert.With);
Assert.IsNull(insert.Output);
Assert.IsNull(insert.Select);
Assert.IsNull(insert.Default);
Assert.IsNull(insert.Execute);
Assert.AreEqual(5, insert.Insert.Tokens.Count);
Assert.AreEqual(8, insert.Values.Tokens.Count);
}
public void InsertStatement_Exec()
{
string sql = @"
INSERT INTO dbo.EmployeeSales
EXECUTE dbo.uspGetEmployeeSales;" ;
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
sql,
includeWhitespace: false);
TSQLInsertStatement insert = statements[0].AsInsert;
Assert.AreEqual(9, insert.Tokens.Count);
Assert.IsNull(insert.With);
Assert.IsNull(insert.Output);
Assert.IsNull(insert.Select);
Assert.IsNull(insert.Default);
Assert.IsNull(insert.Values);
Assert.AreEqual(5, insert.Insert.Tokens.Count);
Assert.AreEqual(4, insert.Execute.Tokens.Count);
}
public void InsertStatement_ValuesWithColumns()
{
string sql = @"
INSERT INTO Production.UnitMeasure
(Name, UnitMeasureCode, ModifiedDate)
VALUES (N'Square Yards', N'Y2', GETDATE());" ;
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
sql,
includeWhitespace: false);
TSQLInsertStatement insert = statements[0].AsInsert;
Assert.AreEqual(22, insert.Tokens.Count);
Assert.IsNull(insert.With);
Assert.IsNull(insert.Output);
Assert.IsNull(insert.Select);
Assert.IsNull(insert.Default);
Assert.IsNull(insert.Execute);
Assert.AreEqual(12, insert.Insert.Tokens.Count);
Assert.AreEqual(10, insert.Values.Tokens.Count);
}
public void InsertStatement_MultipleValues()
{
string sql = @"
INSERT INTO Production.UnitMeasure
VALUES
(N'FT2', N'Square Feet', '20080923'),
(N'Y', N'Yards', '20080923'),
(N'Y3', N'Cubic Yards', '20080923');" ;
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
sql,
includeWhitespace: false);
TSQLInsertStatement insert = statements[0].AsInsert;
Assert.AreEqual(29, insert.Tokens.Count);
Assert.IsNull(insert.With);
Assert.IsNull(insert.Output);
Assert.IsNull(insert.Select);
Assert.IsNull(insert.Default);
Assert.IsNull(insert.Execute);
Assert.AreEqual(5, insert.Insert.Tokens.Count);
Assert.AreEqual(24, insert.Values.Tokens.Count);
}
public void InsertStatement_Select()
{
string sql = @"
INSERT INTO dbo.EmployeeSales
SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY sp.BusinessEntityID, c.LastName;" ;
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
sql,
includeWhitespace: false);
TSQLInsertStatement insert = statements[0].AsInsert;
Assert.AreEqual(55, insert.Tokens.Count);
Assert.IsNull(insert.With);
Assert.IsNull(insert.Output);
Assert.IsNull(insert.Values);
Assert.IsNull(insert.Default);
Assert.IsNull(insert.Execute);
Assert.AreEqual(5, insert.Insert.Tokens.Count);
Assert.AreEqual(50, insert.Select.Tokens.Count);
Assert.AreEqual(4, insert.Select.Select.Columns.Count);
Assert.IsNull(insert.Select.Select.Columns[0].ColumnAlias);
Assert.AreEqual(TSQLExpressionType.Constant, insert.Select.Select.Columns[0].Expression.Type);
Assert.AreEqual("SELECT", insert.Select.Select.Columns[0].Expression.AsConstant.Literal.AsStringLiteral.Value);
Assert.IsNull(insert.Select.Select.Columns[1].ColumnAlias);
Assert.AreEqual(TSQLExpressionType.Column, insert.Select.Select.Columns[1].Expression.Type);
Assert.AreEqual("sp", insert.Select.Select.Columns[1].Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("BusinessEntityID", insert.Select.Select.Columns[1].Expression.AsColumn.Column.Name);
Assert.AreEqual(TSQLExpressionType.Column, insert.Select.Select.Columns[3].Expression.Type);
Assert.AreEqual("sp", insert.Select.Select.Columns[3].Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("SalesYTD", insert.Select.Select.Columns[3].Expression.AsColumn.Column.Name);
}
