Insert statement

• Fit into one line if possible

  Option: fmt039_insert_fit_into_one_line = true, type: TFmtBoolean.

  This option works only length of the whole statement less than max line width.

  INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));

  Option: fmt039_insert_fit_into_one_line = false, type: TFmtBoolean.

  This option works even if the length of the whole statement less than max line width.

  INSERT INTO dbo.Points (PointValue) 
  VALUES (CONVERT(Point, '3,4'));

• Column list/value list fit into one line if possible

  Option: fmt040_insert_column_list_style = fit_into_one_line, type: TFmtListStyle.

  Option: fmt041_insert_value_list_style = fit_into_one_line, type: TFmtListStyle.

  • VALUES keyword left align with INSERT keyword

    INSERT INTO MyLinkServer.AdventureWorks2008R2.HumanResources.Department (Name, GroupName)
    VALUES (N'Public Relations', N'Executive General and Administration');

  • VALUES keyword right align with INTO keyword

    INSERT INTO MyLinkServer.AdventureWorks2008R2.HumanResources.Department (Name, GroupName)
         VALUES (N'Public Relations', N'Executive General and Administration');

• Stacked column list

  • Comma at the end of line

     INSERT INTO Sales.SalesHistory WITH (TABLOCK)
         (SalesOrderID,                   
          SalesOrderDetailID,             
          CarrierTrackingNumber,                                
          ModifiedDate)                   
     SELECT * FROM Sales.SalesOrderDetail;

  • Comma at the begin of line

     INSERT INTO Sales.SalesHistory WITH (TABLOCK)
         (SalesOrderID                   
          ,SalesOrderDetailID             
          ,CarrierTrackingNumber                               
          ,ModifiedDate)                   
     SELECT * FROM Sales.SalesOrderDetail;

  • Comma at the begin of line, align columns

     INSERT INTO Sales.SalesHistory WITH (TABLOCK)
         ( SalesOrderID                   
          ,SalesOrderDetailID             
          ,CarrierTrackingNumber                               
          ,ModifiedDate)                   
     SELECT * FROM Sales.SalesOrderDetail;

  • Comma at the begin of line, align columns, space between comma and column is 1 or n

     INSERT INTO Sales.SalesHistory WITH (TABLOCK)
         (  SalesOrderID                   
          , SalesOrderDetailID             
          , CarrierTrackingNumber                               
          , ModifiedDate)                   
     SELECT * FROM Sales.SalesOrderDetail;

• Stacked value list

  • Comma at the end of line

    INSERT INTO Production.Location WITH (XLOCK)(Name, CostRate, Availability)
    VALUES 
      (N'Final Inventory', 
       15.00, 
       80.00);

  • Comma at the begin of line

    INSERT INTO Production.Location WITH (XLOCK)(Name, CostRate, Availability)
    VALUES 
      (N'Final Inventory' 
       ,15.00, 
       ,80.00);

  • Comma at the begin of line, align columns

    INSERT INTO Production.Location WITH (XLOCK)(Name, CostRate, Availability)
    VALUES 
      ( N'Final Inventory' 
       ,15.00, 
       ,80.00);

  • Comma at the begin of line, align columns, space between comma and column is 1 or n

    INSERT INTO Production.Location WITH (XLOCK)(Name, CostRate, Availability)
    VALUES 
      (  N'Final Inventory' 
       , 15.00, 
       , 80.00);

• Insert ... select, select statement indent from 0 to n

  INSERT TOP (10) INTO HumanResources.NewEmployee 
      SELECT
         e.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,
         a.AddressLine1, a.City, sp.StateProvinceCode, 
         a.PostalCode, e.CurrentFlag
      FROM HumanResources.Employee e
          INNER JOIN Person.BusinessEntityAddress AS bea
          ON e.BusinessEntityID = bea.BusinessEntityID
          INNER JOIN Person.Address AS a
          ON bea.AddressID = a.AddressID
          INNER JOIN Person.PersonPhone AS pp
          ON e.BusinessEntityID = pp.BusinessEntityID
          INNER JOIN Person.StateProvince AS sp
          ON a.StateProvinceID = sp.StateProvinceID
          INNER JOIN Person.Person as c
          ON e.BusinessEntityID = c.BusinessEntityID;